测试表结构如下:
CREATE TABLE [dbo].[tblPhoneNum](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[PhoneNum] [nvarchar](50) NOT NULL,
[name] [nvarchar](50) NOT NULL,
[source] [nvarchar](50) NULL,
[CreateDate] [datetime] NOT NULL,
[CreateUser] [nvarchar](50) NOT NULL,
[UpdateDate] [datetime] NOT NULL,
[UpdateUser] [nvarchar](50) NOT NULL
) ON [PRIMARY]
GO
方案一
每1000条数据执行一次insert 操作。执行效率,工作电脑环境测试,一秒钟一千条。
print 'begin time' + cast(getdate() as nvarchar)
declare @i as bigint
set @i = 1
declare @t as int
set @t = 1
declare @strInsert as nvarchar(max)
declare @sql as nvarchar(max)
set @strInsert =
'insert into
tblPhoneNum
(
PhoneNum
,name
,source
,CreateDate
,CreateUser
,UpdateDate
,UpdateUser
)
values'
while(@i<=15077000)
--while(@i<=100)
begin
while (@t <=1000)
begin
if @t = 1
begin
set @sql =
'(
right(REPLICATE(''0'',11) + cast(' + cast(@i as nvarchar) + ' as varchar),11)
,''username'' + right(REPLICATE(''0'',11) + cast(' + cast(@i as nvarchar) + ' as varchar),11)
,''test source''
,getdate()
,''admin''
,getdate()
,''admin''
)'
end
else
begin
set @sql = @sql +
',(
right(REPLICATE(''0'',11) + cast(' + cast(@i as nvarchar) + ' as varchar),11)
,''username'' + right(REPLICATE(''0'',11) + cast(' + cast(@i as nvarchar) + ' as varchar),11)
,''test source''
,getdate()
,''admin''
,getdate()
,''admin''
)'
end
if @t = 1000
begin
set @sql = @strInsert + @sql
exec sp_executesql @sql
set @sql = ''
set @t = 1
end
else
begin
set @t = @t + 1
end
set @i = @i+1
end
end
print 'end time' + cast(getdate() as nvarchar)
go
方案2:使用表变量,先把数据保存到表变量中,然后再insert select语句插入。
效率提高显著,没十万条数据执行一次插入操作,平均7秒,插入10万条。
create type dt as table
(
PhoneNum VARCHAR(100)
,name VARCHAR(100)
,source VARCHAR(100)
,CreateDate datetime
,CreateUser VARCHAR(100)
,UpdateDate datetime
,UpdateUser VARCHAR(100)
);
GO
select * from systypes
print 'begin time' + cast(getdate() as nvarchar)
declare @dt as dt
declare @i as bigint
set @i = 1
while (@i<=100000)
begin
insert into @dt values
(
right(REPLICATE('0',10) + cast(@i as nvarchar),11),
'username' +right(REPLICATE('0',10) + cast(@i as nvarchar),11),
'test source',
getdate(),
'admin',
getdate(),
'admin'
)
set @i = @i + 1
end
insert into tblPhoneNum
select * from @dt
print 'end time' + cast(getdate() as nvarchar)