Introduction This article (my first) will describe an algorithm that enables a large amount of data to be generated very quickly using a SQL query. The test data can be static or incremental, such as “Item Name” and “Item ID”, respectively, as shown below: Background One of the tasks I did in a project involves generating a testing table with 103,680,000 records. The conventional method of data generation would take a month; hence, a fast method of data insertion was required. The new method took only 5 hours. Using the code Conventional method – Sequential INSERT The conventional way of generating a list of numbers from 0…100000 would be using a loop and an INSERT statement as follows: CREATE TABLE #tempTable([Item ID] [ bigint ], [Item Name] nvarchar ( 30 )) DECLARE @counter int SET @counter = 1 WHILE (@counter 100000 ) BEGIN INSERT INTO #tempTable VALUES (@counter, ' Hammer' ) SET @counter = @counter + 1 END SELECT * FROM #tem...
Comments