Batch generation of millions of data:
Create a view first:
Bytes -------------------------------------------------------------------------------------------------------
Create view v_rand
As
Select Re = stuff (rtrim (cast (RAND () as numeric (7, 6), 1, 2 ,'')
Go
Create a function:
Code
-- Generate a random number Function
Create Function f_randbh (@ bhlen INT)
Returns varchar (50)
As
Begin
Declare @ r varchar (50)
If not (isnull (@ bhlen, 0) between 1 and 50)
Set @ bhlen = 10
Select @ r = char (65
+ (Substring (Re, 1, 1)
+ Substring (Re, 2, 1)
+ Substring (Re, 3, 1) % 26)
+ Char (65
+ (Substring (Re, 4, 1)
+ Substring (Re, 5, 1)
+ Substring (Re, 6, 1) % 26)
From v_rand
While Len (@ r) <@ bhlen
Select @ r = @ r + char (65
+ (Substring (Re, 1, 1)
+ Substring (Re, 2, 1)
+ Substring (Re, 3, 1) % 26)
+ Char (65
+ (Substring (Re, 4, 1)
+ Substring (Re, 5, 1)
+ Substring (Re, 6, 1) % 26)
From v_rand
Return (left (@ r, @ bhlen ))
End
The following shows how to execute SQL to generate a million-level random number: it takes about 3 minutes to generate 2.4 of P4 1 million 1 GB memory in SQL2000.
-- Generate a random number of records with 8-bit length and insert it into the database. (here, the random number starts with a six-digit number and ends with four characters)
Use tempdb Database
Go
Create Table Tb (ID varchar (12 ))
Create unique index ix_tb on TB (ID)
With ignore_dup_key -- ignore duplicate record Indexes
Go
Declare @ DT datetime
Set @ dt = getdate ()
Set nocount off
Declare @ row int
Set @ ROW = 1000000 -- the total number of records is 100 W
While @ row> 0
Begin
Raiserror ('need % d rows ', 10, 1, @ row) with Nowait
Set rowcount @ row
Insert TB select
Id = DBO. f_randbh (4) + right (100000000 + convert (bigint, ABS (checksum (newid (), 6) + DBO. f_randbh (2)
-- From syscolumns C1, syscolumns C2 -- set nocount on 49 second
From syscolumns C1, sysobjects o -- set nocount on 47 second
Set @ ROW = @ row-@ rowcount -- set the number of records to be inserted in the next loop
End
Select begindate = @ DT, enddate = getdate (), second = datediff (second, @ DT, getdate ())
Go
Select count (*) from TB
Go
This executes millions of methods from
Http://www.cnblogs.com/adandelion/articles/542534.html