CASE Study

Sometimes we want to populate a table with random data, mainly for testing purposes. For example, let’s consider the following table:

Transact-SQL

CREATE TABLE Sales.Orders ( Id INT NOT NULL IDENTITY(1,1) , CustomerId INT NOT NULL , DateAndTime DATETIME2(0) NOT NULL , OrderStatus NVARCHAR(50) NULL ); GO

1

2

3

4

5

6

7

8

9

CREATE TABLE

Sales.Orders

(

Id INT NOT NULL IDENTITY(1,1) ,

CustomerId INT NOT NULL ,

DateAndTime DATETIME2(0) NOT NULL ,

OrderStatus NVARCHAR(50) NULL

);

GO

Let’s say we want to insert 100,000 rows with random data. We want to generate random values between 1 and 1,000,000 for the “CustomerId” column, and we want to generate random date & time values in the past 5 years for the “DateAndTime” column. For now, let’s ignore the “OrderStatus” column. Here is how I would do it:

Transact-SQL

INSERT INTO Sales.Orders WITH (TABLOCK) ( CustomerId , DateAndTime ) SELECT TOP (100000) CustomerId = ABS (CHECKSUM (NEWID ())) % 1000000 + 1 , DateAndTime = DATEADD (SECOND , - ABS (CHECKSUM (NEWID ())) % (60 * 60 * 24 * 365 * 5) , SYSDATETIME ()) FROM sys.all_columns AS T1 CROSS JOIN sys.all_columns AS T2; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

INSERT INTO

Sales.Orders WITH (TABLOCK)

(

CustomerId ,

DateAndTime

)

SELECT TOP (100000)

CustomerId = ABS (CHECKSUM (NEWID ())) % 1000000 + 1 ,</