批量插入2W条,貌似没多大空间提升不过你这么简单的语句,就插个1,2,3,为啥不直接写个存储过程在数据库处理 譬如 create proc pr_name @num int as begin while @num<1 begin insert into mytable(a,b) values('test',@num) set @num=@num-1 end end
数据库侧可以批量处理这个问题。 另外找到一张行数超过20000的Table,select top 20000 row_number() over(order by getdate()),'Test' into **** from Table
嗯,再补充一下 select top 20000 row_number() over(order by getdate()),'Test' into **** from sys.objects,sys.objects
--#1.批处理提交.构建好你的SQL语句,只提交一次 insert into mytable(a,b) SELECT 'test1', 1 UNION ALL SELECT 'test2', 2 UNION ALL SELECT 'test3', 3 --#2.如果只是固定数据的插入,则参考如下: insert into mytable(a,b) SELECT TOP(2000) 'test', ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM sys.columns a, sys.columns b
用事务包装for i = 1 to 20000begin tran 插入1w行 commitnext
譬如
create proc pr_name
@num int
as
begin
while @num<1
begin
insert into mytable(a,b) values('test',@num)
set @num=@num-1
end
end
数据库侧可以批量处理这个问题。
另外找到一张行数超过20000的Table,select top 20000 row_number() over(order by getdate()),'Test'
into ****
from Table
select top 20000 row_number() over(order by getdate()),'Test'
into ****
from sys.objects,sys.objects
insert into mytable(a,b)
SELECT 'test1', 1 UNION ALL
SELECT 'test2', 2 UNION ALL
SELECT 'test3', 3
--#2.如果只是固定数据的插入,则参考如下:
insert into mytable(a,b)
SELECT TOP(2000) 'test', ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM sys.columns a, sys.columns b
插入1w行
commitnext