I'm doing a project in ms sql and I have to fill table with random records.
Here is a definition of
create table invoice( id_invoice INT PRIMARY KEY IDENTITY(1,1), id_employee INT FOREIGN KEY REFERENCES Employee(id_employee), id_client INT FOREIGN KEY REFERENCES Client(id_client), id_service INT FOREIGN KEY REFERENCES Service(id_service), amount MONEY, payment_way VARCHAR(20) CHECK (payment_way in ('cash', 'credit_card')) );
And here is the code that generates random records:
ALTER PROCEDURE generate_invoices AS BEGIN DECLARE @id_employee INT DECLARE @id_client INT DECLARE @id_service INT DECLARE @amount INT DECLARE @payment_way VARCHAR(20) SET @id_employee = (SELECT TOP 1 id_employee FROM Employee ORDER BY NEWID()) SET @id_client = (SELECT TOP 1 id_client FROM Client ORDER BY NEWID()) SET @id_service = (SELECT TOP 1 id_service FROM Service ORDER BY NEWID()) EXEC random 1000, 5000, @amount OUTPUT IF (@amount % 2 = 1) SET @payment_way = 'cash' ELSE SET @payment_way = 'credit card' INSERT INTO Invoice VALUES (id_employee, @id_clienta, @id_service, @amount, @payment_way) END GO EXEC generate_invoices GO 10
The code works obviously, but I would like to ask if there is better way (shorter, more efficient) to do same thing, because I must write over a dozen of similar procedure for other tables.