SQLTeam.com | Weblogs | Forums

Random data


#1

I want a query to generate the random data 1 million records in the table

TableId, Name


1 username1
1 username2
1 username3
1 username4
1 username5

Thanks for your help..


#2
;WITH N(n) AS
(
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
	SELECT 1 UNION ALL SELECT 1 UNION ALL 
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
	SELECT 1 UNION ALL SELECT 1
)
INSERT INTO yourTable (TableId, Name)
SELECT 1, CHECKSUM(NEWID())
FROM 
	N a CROSS JOIN N b CROSS JOIN N c CROSS JOIN
	N d CROSS JOIN N e CROSS JOIN N f;

#3

Thanks for immediate response

May be i missed out my requirement..

I want the script which should generate the data like the below in the table

The column1 should be with 1 always and column2 with username + auto increment number..

expected data below:

1 username1
1 username2
1 username3
1 username4
1 username5


#4

But that is not random, it has a specific predictable result! :smile:

In any case:

;WITH N(n) AS
(
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
	SELECT 1 UNION ALL SELECT 1 UNION ALL 
	SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
	SELECT 1 UNION ALL SELECT 1
)
INSERT INTO yourTable (TableId, Name)
SELECT 1, 'username'+CAST(ROW_NUMBER() OVER (ORDER BY (SELECT null)) AS VARCHAR(8))
FROM 
	N a CROSS JOIN N b CROSS JOIN N c CROSS JOIN
	N d CROSS JOIN N e CROSS JOIN N f;

#5

Thanks a lot it worked..


#6

Do like I did... imagine that you sorted it by NEWID() and you'll feel much better. :wink: