首页 > 代码库 > sql server中的大数据的批量操作（批量插入，批量删除）
sql server中的大数据的批量操作（批量插入，批量删除）
首先我们建立一个测试用员工表
---创建一个测试的员工表--- create table Employee( EmployeeNo int primary key, --员工编号 EmployeeName nvarchar(50) null, --员工名称 CreateUser nvarchar(50) null, --创建人 CreateDate datetime null, --创建时间 )
执行后结果：
那么假如我们要批量插入10000条数据，应该怎么办？
这里有四种方法（普通循环，事务循环、批量插入、cte插入）
1、普通循环插入（while）
/******************************************* ***普通循环(插入数据10000，执行时间：1283毫秒) ********************************************/ --开启开关（记录sql语句各个阶段所消耗的时间）--- set statistics time on; --声明两个变量--- declare @Index int; declare @Timer datetime; --对两个变量进行赋值---- set @Index = 1; set @Timer = GETDATE(); --当循环小于1000次执行添加语句--- while @Index <=10000 begin --执行添加的语句-- insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate) values(@Index,‘员工‘+ cast(@Index as CHAR(5)),‘system‘,GETDATE()) --设置循环次数加1 set @Index = @Index+1 end --获取执行的毫秒数-- select DATEDIFF(MS,@Timer,GETDATE()) as ‘执行时间（毫秒）‘ --关闭开关（记录SQL语句各阶段所消耗的时间） set statistics time off;
执行普通循环插入10000条数据，大概需要1200多毫秒，结果如图所示
2、事务循环插入
/******************************************* ***事务循环(插入数据1000，执行时间：460毫秒) ********************************************/ --开启事务-- begin tran; --开启开关（记录sql语句各个阶段所消耗的时间）--- set statistics time on; --声明两个变量--- declare @Index int; declare @Timer Datetime; --对两个变量进行赋值---- set @Index=1; set @Timer = GETDATE(); --当循环小于1000次执行添加语句--- while @Index <=10000 begin --执行添加的语句-- insert into Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate) values(@Index,‘员工‘+ cast(@Index as CHAR(5)),‘system‘,GETDATE()) --设置循环次数加1 set @Index = @Index+1 end --获取执行的毫秒数-- select DATEDIFF(MS,@Timer,GETDATE()) as ‘执行时间（毫秒）‘ set statistics time off; --提交事务-- commit;
执行事务循环插入10000条数据，大概需要400多毫秒，结果如下所示：
3、批量插入
/******************************************* ***批量插入(插入数据10000，执行时间：33毫秒) ********************************************/ --开启开关（记录sql语句各个阶段所消耗的时间）-- set statistics time on; --声明一个时间变量--- declare @Timer datetime; ---对时间变量进行赋值--- set @Timer = GETDATE(); ---执行批量操作的sql语句--- insert Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate) select top(10000) EmployeeNo=ROW_NUMBER() over( order by c1.[object_id]),‘员工‘,‘system‘,GETDATE() from sys.columns as c1 cross join sys.columns as c2 order by c1.object_id --获取执行的毫秒数-- select DATEDIFF(MS, @Timer, GETDATE()) AS [执行时间(毫秒)]; --关闭开关（记录SQL语句各阶段所消耗的时间）-- SET STATISTICS TIME OFF;
执行批量插入10000条数据，大概只要33毫秒，结果如图所示：
4、CTE插入
--/******************************************* --***CTE插入(插入数据10000，执行时间：40毫秒) --********************************************/ --开启开关（记录sql语句各个阶段所消耗的时间）-- set statistics time on; --声明一个时间变量并赋值-- declare @Timer datetime = GETDATE(); ---将要添加10000条语句组合成CTE模块--- ;with CTE(EmployeeNo,EmployeeName,CreateUser,CreateDate) as ( select top(10000) EmployeeNo = ROW_NUMBER() over (order by C1.[OBJECT_ID]), ‘员工‘, ‘system‘, GETDATE() from SYS.COLUMNS as C1 cross join SYS.COLUMNS as C2 order by C1.[OBJECT_ID] ) --执行CTE插入语句--- insert Employee select EmployeeNo,EmployeeName,CreateUser,CreateDate from CTE; --获取执行的毫秒数-- select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)]; ---关闭开关（记录sql语句各个阶段所消耗的时间）--- set statistics time off;
执行插入10000条数据，大概需要40毫秒，结果如图所示：
最后我们查看一下，批量插入10000条数据的员工表
小结：
1）按执行时间，效率依次为：CTE和批量插入效率相当，速度最快，事务插入次之，单循环插入速度最慢；
2）单循环插入速度最慢是由于INSERT每次都有日志，事务插入大大减少了写入日志次数，批量插入只有一次日志，CTE的基础是CLR，善用速度是最快的
那么，假如我们要批量删除我们插入的数据，怎么办呢？
批量删除有3中方法（循环删除、批量删除、truncate 删除）
1、循环删除
--/******************************************* --***循环删除(删除数据10000，执行时间：20毫秒) --********************************************/ set statistics time on; --声明一个时间变量--- declare @Timer datetime = GETDATE(); --删除语句-- delete from Employee --获取执行的毫秒数-- select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)]; set statistics time off;
删除10000条数据，所需的时间大概为20毫秒，如下所示：
2、批量删除
/******************************************* ***批量删除(删除数据10000，执行时间：23毫秒) ********************************************/ set statistics time on; declare @Timer datetime = GETDATE(); SET ROWCOUNT 10000; while 1 = 1 begin --开启事务-- begin tran --执行删除-- delete from Employee; --提交事务-- commit; IF @@ROWCOUNT = 0 break; end set ROWCOUNT 0; --获取执行的毫秒数--- select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)]; set statistics time off;
删除10000条数据，所需的时间大概为23毫秒，如下所示：
3、truncate删除
--/******************************************* --***truncate删除(删除数据10000，执行时间：3毫秒) --********************************************/ set statistics time on; --声明一个时间变量-- declare @Timer datetime = getdate(); --执行truncate语句-- truncate table Employee ---获取执行的毫秒数--- select DATEDIFF(MS, @Timer, GETDATE()) as [执行时间(毫秒)] set statistics time off
删除10000条数据，所需的时间大概为3毫秒，如下所示：
小结：
1）TRUNCATE太快了，清除10W数据一点没压力，批量删除次之，最后的DELTE太慢了
2）TRUNCATE快是因为它属于DDL语句，只会产生极少的日志，普通的DELETE不仅会产生日志，而且会锁记录
PS:
参考学习网址：http://www.cnblogs.com/panchunting/archive/2013/04/27/SQL_Tech_001.html
sql server中的大数据的批量操作（批量插入，批量删除）
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。