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.

Comments

Popular posts from this blog

SQL Reporting Services - Viewer broken in Non-IE Browsers: FIX

Dynamics AX 2012 - Visual Studio Team Services Custom Work Item Fields Break Version Control (Error TF237121)

Dynamics AX SysFileDeployment Framework - Deploy files automatically (using resources) to the client on login