Create Auto Insert Script - 500k records to load data into sql server

Transact-SQL
1

Hi,

I would like to Create Auto Insert Script - 500k records to load data into sql server for my

testing.

How can i generate dummy table and data script?

I have sql server 2008 and 2012 version.

Thanks

2

I have used this one in the past

http://www.generatedata.com/

3

Depending on the type of test data I'm need to generate, I tend to use some variation of the following...

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
DROP TABLE #TestData;

CREATE TABLE #TestData (
	ID INT NOT NULL PRIMARY KEY,
	RandomINT_1 INT NOT NULL,
	RandomINT_2 INT NOT NULL,
	RandonDate_1 DATE NOT NULL,
	RandomDate_2 DATE NOT NULL,
	RandomTesxString VARCHAR(10)
	);

WITH 
	cte_n1 (n) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (n)), 
	cte_n2 (n) AS (SELECT 1 FROM cte_n1 a CROSS JOIN cte_n1 b),
	cte_n3 (n) AS (SELECT 1 FROM cte_n2 a CROSS JOIN cte_n2 b),
	cte_Tally (n) AS (
		SELECT TOP 500000	--<< set num of rows here...
			ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
		FROM
			cte_n3 a CROSS JOIN cte_n3 b
		)
INSERT #TestData (ID, RandomINT_1, RandomINT_2, RandonDate_1, RandomDate_2, RandomTesxString)
SELECT 
	ID = t.n, 
	RandomINT_1 = ri.RandomINT, 
	RandomINT_2 = ri.RandomINT + ABS(CHECKSUM(NEWID()) % 100),
	RandonDate_1 = rd.RandomDate,
	RandonDate_2 = DATEADD(dd, ABS(CHECKSUM(NEWID()) % 180) + 1, rd.RandomDate),
	RandomTesxString = CONCAT(
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 65),
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 97),
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 97),
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 97),
								' ',
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 65),
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 97),
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 97),
								CHAR(ABS(CHECKSUM(NEWID()) % 26) + 97)
							)
FROM
	cte_Tally t
	CROSS APPLY ( VALUES (ABS(CHECKSUM(NEWID()) % 100 + 1)) ) ri (RandomINT)
	CROSS APPLY ( VALUES (DATEADD(dd, CHECKSUM(NEWID()) % 300, '2017-01-01')) ) rd (RandomDate);


SELECT COUNT(*) FROM #TestData td;

SELECT TOP 100 * FROM #TestData td;