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(iddateoforderamountlocationid) 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 idamount 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(11) 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 manualllyas 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