Stored Procedure

Question

Location (Location_id  int,  Location_name  nvarchar(50))
Sales  (id int  ,Date_of_order date, Amount int,  Location_id int)
Location_id is foreign key in sales.
How to insert n values into Sales table.All elements should b added randomly using random or any function which generates random numbers

Pradip Pandey · Answer

Create a trigger on Location table
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO
ALTER trigger SalesInsertTrigger on Location FOR INSERT AS BEGIN
Declare @locationid int Set @locationid = (Select top 1 locationid from Location order by locationid desc) set nocount on     insert into Sales(id,dateoforder,amount,locationid) values(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)),getdate(),ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)),@locationid)
END
When you insert a location into location table, one record will be inserted with respect to the added locationid and values for id,amount of the sales table will be added with random numbers. Also don't set primery key to the field locationid in the location table, only set the properties identity as true with increment(1,1) and create a foreign key constraint between location and sales table.
Hope it will help.

Priyank · Answer

Thanks for ur reply.
Location (Location_id  int,  Location_name  nvarchar(50))
Sales  (id int  ,Date_of_order date, Amount int,  Location_id int)
Location_id is foreign key in sales.
Location details are filled.
Now the question is how to insert n values(n can be 10 or 100) into Sales table.
User need n0t enter all the values manuallly,as all the fields in sales table are of int type using random number function they have to be assigned.

Vithal Wadje · Answer

explain in detail