How to generate data in SQL Server

‘How to generate data in SQL Server’ is a very interesting question, since there is not only one correct answer. It really depends on what the purpose is.

I talked to Pinal Dave (blog: SQLAuthority.com) earlier, and he suggested the following script:

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
USE tempdb
 GO
 -- Create Table OneIndex with few columns
 CREATE TABLE OneIndex (ID INT,
 FirstName VARCHAR(100),
 LastName VARCHAR(100),
 City VARCHAR(100))
 GO
 -- Insert One Hundred Thousand Records
 INSERT INTO OneIndex (ID,FirstName,LastName,City)
 SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
 'Bob',
 CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
 ELSE 'Brown' END,
 CASE
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Las Vegas'
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
 WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
 ELSE 'Houston' END
 FROM sys.all_objects a
 CROSS JOIN sys.all_objects b
 GO

This is one way to do it, if our goal is to insert the data at once. If this is our goal, then this is a great script.

But what if we wanted to insert the data continuously and in increments over a certain time period?

I will just take the above script and have it insert 100,000 rows, but in increments of 1000 rows every 5 seconds.

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
USE tempdb
GO
-- Create Table OneIndex with few columns
CREATE TABLE OneIndex (ID INT,
FirstName VARCHAR(100),
LastName VARCHAR(100),
City VARCHAR(100))
GO
-- Insert One Hundred Thousand Records
INSERT INTO OneIndex (ID,FirstName,LastName,City)
SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
'Bob',
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name)%2 = 1 THEN 'Smith'
ELSE 'Brown' END,
CASE
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%1000 = 1 THEN 'Las Vegas'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 1 THEN 'New York'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 5 THEN 'San Marino'
WHEN ROW_NUMBER() OVER (ORDER BY a.name)%10 = 3 THEN 'Los Angeles'
ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
waitfor delay '00:00:05'
GO 100

What this concept proves is that there are many ways to treat data in SQL Server database, i.e. either as a whole, or as an incremental events of data manipulation over a time slice.

Thanks to Pinal once again for the script.

 

Comments are closed.