SQL Server creates random test data

In the process of data warehouse development, we often need to insert a lot of test data to test the database query performance and calculate the storage space occupied. This article mainly introduces the tool that does not borrow the third party to generate a lot of test data directly in the database.

Demand

1. Each row contains 5 date fields and a 10 character string as the primary key;
2. All dates should be in the data from today 2 years ago;
3. Each line should have a different date of 1;
4. You can create indexes by primary key and date fields.

Create a test table

CREATE TABLEdbo. Testtablesize (MykeyfieldVARCHAR(Ten) not NULL, MyDate1DATETIME  not NULL, MyDate2DATETIME  not NULL, MyDate3DATETIME  not NULL, MyDate4DATETIME  not NULL, MyDate5DATETIME  not NULL)

Generate Test Code

DECLARE @RowCount INT DECLARE @RowString VARCHAR(Ten) DECLARE @Random INT DECLARE @Upper INT DECLARE @Lower INT DECLARE @InsertDate DATETIME SET @Lower = -730 SET @Upper = -1 SET @RowCount = 0  while @RowCount < 3000000 BEGIN     SET @RowString = CAST(@RowCount  as VARCHAR(Ten))     SELECT @Random = ROUND(((@Upper - @Lower -1)* RAND()+ @Lower),0)     SET @InsertDate = DATEADD(DD,@Random,GETDATE())     INSERT  intotesttablesize (Mykeyfield, MyDate1, MyDate2, MyDate3, MyDate4, MyDate5)VALUES(REPLICATE('0',Ten - datalength(@RowString))+ @RowString,@InsertDate,DATEADD(DD,1,@InsertDate) ,DATEADD(DD,2,@InsertDate) ,DATEADD(DD,3,@InsertDate) ,DATEADD(DD,4,@InsertDate))     SET @RowCount = @RowCount + 1 END

Summarize

This article simply presents a brief overview of the method to insert random test data into a database table. It is also proposed to calculate a random number, and the method based on random offset random data input. This may not be the best way to create a random dataset, and if you don't have any popular third-party tools, it's really a good choice.

SQL Server creates random test data