SQL SERVER Bulk Build number

Begin:

In testing, in order to simulate orders, there is a requirement for the DBA, how to quickly batch generate orders through the background database script.

Analysis

Standing in the database perspective, bulk generate orders, which is the bulk of the row data in the table.

In SQL, two table (e.g. A, B) can be combined with a cross join to form a Cartesian product, 1

　　

Figure 1

If, for the combined result of Figure 1, a combination of iterations, then you can get a result of 16 rows, 2:

　　

Figure 2

In SQL Sever, the following SQL statement analyzes the need for a, B table combination, how many times an iteration can generate records for millions of rows,

　　

 UsetempdbGoDeclare @x bigint =2Declare @i int=1 while(1=1)begin        Set @x=Square(@x)    if @ @ERROR<>0  Break; Print RTrim(@i)+' : '+RTrim(sqrt(@x))+'x'+RTrim(sqrt(@x))+' = '+RTrim(@x); Set @i+=1;End

From this you can see a A-B table combination, which requires iterating 5 times to generate millions of rows of data.

Realize:

SQL SERVER Code:

; withA0 as(SelectId=1 Union  All SelectId=1), A1 as(Selecta.ID fromA0 a,a0 b), A2 as(Selecta.ID fromA1 a,a1 B), A3 as(Selecta.ID fromA2 a,a2 b), A4 as(Selecta.ID fromA3 a,a3 B), T as(SelectId=Row_number () Over(Order  bya.ID) fromA4 a,a4 b)Select Top 1000000Id fromT

Here, it is possible to generate data rows in batches. Additional conditions or data can be attached to meet the needs of the beginning, depending on the actual needs.

SQL SERVER Bulk Build number