[SOLVED] SQL query to fast data generation

SQL query to fast data generation

I have created a query to generate some data to sql databases, but generation of 1 GB data takes about 45 minutes. How to increase a performance of data generation?

DECLARE @RowCount INT
DECLARE @RowString VARCHAR(10)
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(10))
 SELECT @Random = ROUND(((@Upper - @Lower -1) * RAND() + @Lower), 0)
 SET @InsertDate = DATEADD(dd, @Random, GETDATE())

 INSERT INTO Table_1
  (q
  ,w
  ,e
  ,r
  ,t
  ,y)
 VALUES
  (REPLICATE('0', 10 - 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

Solution

You may try following also:

;with seq as (
    select top (3000000) N = row_number() over (order by @@spid) - 1 from sys.all_columns c1, sys.all_columns c2
)
INSERT INTO Table_1 (q, w, e, r, t, y)
select
    right('0000000000' + cast(N as varchar(10)), 10)
    ,p.InsertDate
    ,DATEADD(dd, 1, p.InsertDate)
    ,DATEADD(dd, 2, p.InsertDate)
    ,DATEADD(dd, 3, p.InsertDate)
    ,DATEADD(dd, 4, p.InsertDate)
from seq
    cross apply (select DATEADD(dd, ROUND(((@Upper - @Lower -1) * RAND(checksum(newid())) + @Lower), 0), GETDATE())) p(InsertDate)