Random data

Transact-SQL
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: