INSERT lots of records using SQL Server 2008

sql, sql-server, sql-server-2008, t-sql

Solution

Doing it all in one statement with no loops will be the most efficient way.

INSERT INTO [lotto].[dbo].[CustomerSelections]
           ([draw_date]
           ,[val1]
           ,[val2]
           ,[val3]
           ,[val4]
           ,[val5]
           ,[val6])
SELECT TOP (100000 )
           '2013-07-05',
           1 + ABS(CRYPT_GEN_RANDOM(8) % 49),
           1 + ABS(CRYPT_GEN_RANDOM(8) % 49),
           1 + ABS(CRYPT_GEN_RANDOM(8) % 49),
           1 + ABS(CRYPT_GEN_RANDOM(8) % 49),
           1 + ABS(CRYPT_GEN_RANDOM(8) % 49),
           1 + ABS(CRYPT_GEN_RANDOM(8) % 49)
FROM master..spt_values v1,
     master..spt_values v2

Problem

I think i have cracked a way of generating the random numbers. But whats the best way in SQL server to loop this efficiently? I have the following SQL: ``` DECLARE @Random1 INT; DECLARE @Random2 INT; DECLARE @Random3 INT; DECLARE @Random4 INT; DECLARE @Random5 INT; DECLARE @Random6 INT; DECLARE @Upper INT; DECLARE @Lower INT; ---- This will create a random number between 1 and 49 SET @Lower = 1 ---- The lowest random number SET @Upper = 49 ---- The highest random number SELECT @Random1 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SELECT @Random2 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SELECT @Random3 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SELECT @Random4 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SELECT @Random5 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) SELECT @Random6 = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0) --SELECT @Random; INSERT INTO [lotto].[dbo].[CustomerSelections] ([draw_date] ,[val1] ,[val2] ,[val3] ,[val4] ,[val5] ,[val6]) VALUES ( '2013-07-05' ,@Random1 ,@Random2 ,@Random3 ,@Random4 ,@Random5 ,@Random6 ) ``` What is the best way to get SQL Server to run this SQL 100,000 times?

Original source