create table #testTB ( testid nvarchar(20) ) declare @num int set @num =1 while(1=1) begin insert into #testTB(testid) values( rand(abs(convert(int,checksum(newid()))))) set @num=@num+1 if(@num > 1000000) return; end
drop table #testTB create table #testTB ( testid nvarchar(50) ) go DECLARE @myid uniqueidentifier SET @myid = NEWID() insert into #testTB(testid) values(CAST( @myid as nvarchar(50))) go 100 select * from #testTB就是这个!
use test go if object_id('myTable') Is not null Drop table myTable go Create table myTable(data nvarchar(50)) godeclare @maxID int set @maxID =1000000;;with L0 as (select 1 as ID union all select 1), L1 as (select a.ID from L0 as a , L0 as b), L2 as (select a.ID from L1 as a , L1 as b), L3 as (select a.ID from L2 as a , L2 as b), L4 as (select a.ID from L3 as a , L3 as b), L5 as (select a.ID from L4 as a , L4 as b), SeqNo as (select row_number()over(order by ID) as ID from L5)Insert into myTable(data) select rand(abs(convert(int,checksum(newid()))))*10000 from SeqNo where ID<=@maxIDgo 生成100W条数据使用不到4秒，环境是Win7 + SQL Server 2012
非CTE方法,直接Insert方法： if object_id('myTable') is not null drop table myTablecreate table myTable(data nvarchar(50)) go Set nocount ondeclare @maxID int,@r int select @maxID =1000000,@r =1insert into myTable(Data) values(rand(abs(convert(int,checksum(newid()))))*10000)while @r*2<=@maxID begin insert into myTable(Data) select rand(abs(convert(int,checksum(newid()))))*10000 from myTable
set @r=@r*2 endif Exists(Select 1 From myTable Having count(1)<@MaxID) insert into myTable(Data) select top(@MaxID-@r) rand(abs(convert(int,checksum(newid()))))*10000 from myTablego
不知道你要测试的数据是什么？ 一个简单的办法是:
1. CREATE TABLE [test]( [id] [int] IDENTITY(1,1) NOT NULL, [txt] [nchar](10) NULL ) ON [PRIMARY]2. INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345') INSERT INTO [test] ([txt]) VALUES ('abcde12345')3. INSERT INTO test(txt) SELECT txt FROM test AS test_1 把第3步重复运行直到达到你的要求。
(
testid nvarchar(20)
)
declare @num int
set @num =1
while(1=1)
begin
insert into #testTB(testid) values( rand(abs(convert(int,checksum(newid())))))
set @num=@num+1
if(@num > 1000000)
return;
end
create table #testTB
(
testid nvarchar(50)
)
go
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
insert into #testTB(testid) values(CAST( @myid as nvarchar(50)))
go 100
select * from #testTB
testid
--------------------------------------------------
93F731C3-46CA-4D12-89CF-27154550BB73
D6FBAEA3-DABB-453E-BC2D-32C3A70C23F3
651A345B-77B1-4073-ABFB-E112005685E4
54D412DC-3F1D-4284-8D2F-61ABFA6D78F4
EE8EB30A-71C3-496F-9C22-91B0A8051315
46911489-BCFC-4B04-8CCE-BD2C4280B9ED
B303D2AF-F1D7-4030-B9E1-0AAB28D7A8AC
6071D940-42E5-40F5-B654-05F0526D2125
55ED3B8A-91E3-4DA2-90C4-2D91EE02066E
9BD2B185-B096-4A85-B42B-99DE7631FDDF
7B76EF20-74DC-4523-A3FF-985508DEC68C
D73CA66E-6FF6-44B4-B2F8-F96C12B7BD0B
A8CAE245-C08F-4EF3-9706-164324F11124
FB63C2C1-52CB-471A-A000-F888CCD98C4F
347A6E45-8AEF-4DA0-B03D-7A304D9717FF
FD7662D9-456B-47AE-AE2B-6846143549F8
1F09C1F0-DD5D-4D77-A243-6D6708E231D9
AAEF3140-4C90-4D5C-8BFF-357DD033D6A3
B456A61A-ACD0-448D-A2FA-4E7CE29881DE
2878E4C2-EEEB-4CC2-8803-D349ACBBA140
F4A84C13-7254-428E-A40E-A66081A7FA51
974C9105-2113-400A-8DAF-ED2337E63A38
A3BF6C66-159B-4B94-8215-29CB261CC66C
2F057C2F-368D-4E13-9D2A-E72383542023
8D2B7DD4-1792-42EE-8679-5011D4E2D10E
BD184120-FA6B-4783-8A01-07DB77F2595A
7DA9917F-265C-4665-8B12-3845CD9DA5D0
B24F2A0F-B8C2-46C4-84D9-C9E78554B4E5
13E60A75-B3DD-48D9-AE5D-77828AEDBCF1
09B5B9B9-6414-43FF-A3B8-00F5DC791B07
A3698926-6A31-43C2-9FF9-13A6B06E477A
39B671BA-9ECF-4C6F-A244-C5C6498BEFB3
3249F2F0-D345-4520-B51A-10E1869339A3
BA9518DF-4719-471B-ABE1-691F8A8DD544
50E85266-7452-4B28-A7B3-E9C7A4A4C711
B42CD27A-F09D-40C5-9D9E-73B9F3EBC463
AB935884-FD25-44C8-B5AD-C3E73F50FD56
231D4703-BAB3-4D08-8C0A-93933125857C
19746AFF-2B44-46C9-9477-F9F6D1828734
E3B8CA8A-3719-4079-AA96-0C68F934C5A5
37F21998-1202-41EB-AED8-941C44F4036F
E306FE13-1A34-46F7-BB18-9C85657D671C
1B2CD51D-91C9-4A53-829D-263EDF1D73EB
F52B2452-D667-4C3B-913D-9BEC90E04B34
9911130C-4351-4FB1-BB83-62AA112C70D8
F231475E-2E22-474E-834F-42556C647FD7
97F778E0-0EDF-4F29-AEA8-91F1E7DE93BC
3D1CF451-D7DE-4AAB-85A8-CC82F664B80D
62197C71-0CB9-4414-9026-C9702BF67DF5
BF24E8DF-58A0-4B8D-9675-91219F8EBFE0
EE466428-678D-4419-8E6A-804AE9C36920
AAD03C61-E986-4F66-ABAC-520E0B32894C
D8E378F9-22B5-485B-B7C6-80B12A081649
5A974DD9-CC83-42C0-8492-C19007A94C3A
6B9BBD52-53F9-4E12-9324-4D190905083D
02B6687E-A7E0-43E7-B69D-DCEC4ECDE1C0
4008F7C5-2ABC-4FF6-8427-4D9F0BA13BA8
46A828BF-B780-411A-9D80-32E1B5C08E90
F55A0302-9A44-4B8D-9EA5-2BB66AC3C601
583D17E3-9853-412B-833C-048E58D0FEBA
98F702F2-E7CB-4FA8-94B2-6678F0C90482
A6991799-ADF7-4546-ADF0-9F5BC82EBE1A
5918E7CE-A208-4DDC-9B4E-D009133CB482
4CCBA0C3-5B9B-486E-98E4-1C037E74A54A
A438940F-F2AE-4858-987A-BD12D8EEFA72
13B6D58B-14A7-48A5-BC30-D0AB938906EB
F779766E-0C77-4A80-AD6A-BA66113D7723
42F78184-3BA6-411A-A625-A84014A76B75
FD117E57-891A-468B-9799-ACE0AEAB9FEF
F9B36520-B95F-40AA-A462-511D4A136C26
4A62F32D-5CFA-44C1-BD72-B1174B61C7E5
293B33DC-5582-45F7-840D-E09205D11F78
43936AD1-0361-43DD-A285-83E7F493D800
11414B05-31C9-4717-BDEB-7D5403D0A118
C07A002F-26D9-4B5D-9664-982BDBD94C62
9A7917D1-AB58-46CD-8DAA-5A3EBBFEC57E
745C886E-E089-45E6-A3DB-C4D7A40C5412
13511911-2007-446A-8BBC-208557E7EAF9
AEFDD3ED-C05E-443E-BAA0-2154E585A2DF
B74C9EAB-510E-4A71-8982-025AB4B8A305
C6DC1A49-BEA2-48C9-AE9E-40837D50F28B
C17D3271-61C5-4F3D-9A67-02B61A632A20
CB6E35F6-A780-41AF-BAF4-850F57247120
F40DB9FB-EC53-4ED0-AAA4-EF7108A5630E
BCF5EA53-6797-434F-9F9F-965338A2098A
65A84D6A-895F-4323-A6D3-3A225B62761E
CA06C3A1-E6C0-46B5-BB2F-403F95912383
9867EC48-7700-43F2-8CB7-79E4BB50B584
FF1F9C6B-27B0-40C6-BE87-80A60F9D949E
BCAE848F-6A01-4522-9380-D637C581B9A4
3D086DC1-B019-4BE2-AA5F-70A02DD013E8
9744C1F9-0132-441F-BC71-4DFD6A99834E
C4EFD5B9-6161-4F77-8E78-258487FF8FE1
8564AAE0-F632-457D-8BCD-CA0E96A6696D
45A24ED3-7BA0-4E40-8987-FE0BF424CD9B
3C46E7D8-FF58-43EA-B5F2-8BB2C86FD8CB
819752E8-C5A1-4892-9846-27AC68C3A5E8
E2A73410-5E85-4DC7-B351-AD23F3F98412
C21D5BAC-FE06-42E9-B9E6-043C9C9569B0
BE9408DC-8F7F-4D39-BAE0-85515ED15F69(100 行受影响)
insert into 表名
select 字段1，字段2，字段3.union
select 字段1，字段2，字段3.union
select 字段1，字段2，字段3.union
select 字段1，字段2，字段3.union
select 字段1，字段2，字段3.union
...
...
...
select 字段1，字段2，字段3.
最后1条要把union去掉呢。
不然会有问题的。用1条sql语句就可以搞定了。
create table #testTB
(
testid nvarchar(50)
)
go
DECLARE @myid uniqueidentifier
SET @myid = NEWID()
insert into #testTB(testid) values(CAST( @myid as nvarchar(50)))
go 100
select * from #testTB就是这个!
use test
go
if object_id('myTable') Is not null Drop table myTable
go
Create table myTable(data nvarchar(50))
godeclare @maxID int
set @maxID =1000000;;with
L0 as (select 1 as ID union all select 1),
L1 as (select a.ID from L0 as a , L0 as b),
L2 as (select a.ID from L1 as a , L1 as b),
L3 as (select a.ID from L2 as a , L2 as b),
L4 as (select a.ID from L3 as a , L3 as b),
L5 as (select a.ID from L4 as a , L4 as b),
SeqNo as (select row_number()over(order by ID) as ID from L5)Insert into myTable(data)
select rand(abs(convert(int,checksum(newid()))))*10000 from SeqNo where ID<=@maxIDgo
生成100W条数据使用不到4秒，环境是Win7 + SQL Server 2012
if object_id('myTable') is not null
drop table myTablecreate table myTable(data nvarchar(50))
go
Set nocount ondeclare @maxID int,@r int
select @maxID =1000000,@r =1insert into myTable(Data) values(rand(abs(convert(int,checksum(newid()))))*10000)while @r*2<=@maxID
begin
insert into myTable(Data) select rand(abs(convert(int,checksum(newid()))))*10000 from myTable
set @r=@r*2
endif Exists(Select 1 From myTable Having count(1)<@MaxID)
insert into myTable(Data)
select top(@MaxID-@r) rand(abs(convert(int,checksum(newid()))))*10000 from myTablego
不知道你要测试的数据是什么？ 一个简单的办法是:
1.
CREATE TABLE [test](
[id] [int] IDENTITY(1,1) NOT NULL,
[txt] [nchar](10) NULL
) ON [PRIMARY]2.
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')
INSERT INTO [test] ([txt]) VALUES ('abcde12345')3.
INSERT INTO test(txt)
SELECT txt
FROM test AS test_1
把第3步重复运行直到达到你的要求。