SQL - Test Data - Quick and Easy

Often I find myself needing large amounts of test data in SQL, either for performance testing or simply to have something to work with on the UI.  Fortunately this little SQL gem does the trick, and quickly too!



The following query will insert several hundred records (673 in Northwind, though this will depend on the database) in less than a second.

insert into [Northwind].[dbo].[Employees]

           ([LastName]

           ,[FirstName])

     select

           substring(cast(newid() as varchar(36)), 0, 20)

           ,substring(cast(newid() as varchar(36)), 0, 10)

           from sys.columns T1

Need more records?  Try cross joining.  This query will insert several hundred thousand records in less than a minute (673 x 673 = 452929 in 18 seconds for Northwind).

insert into [Northwind].[dbo].[Employees]

           ([LastName]

           ,[FirstName])

     select

           substring(cast(newid() as varchar(36)), 0, 20)

           ,substring(cast(newid() as varchar(36)), 0, 10)

           from sys.columns T1 CROSS JOIN sys.columns T2


Need a database to test on?  Here's a script for the Northwind Database.