sql server中的大数据的批量操作（批量插入，批量删除）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了sql server中的大数据的批量操作（批量插入，批量删除），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4614字，纯文字阅读大概需要7分钟。
内容图文
首先我们建立一个测试用员工表
--
-创建一个测试的员工表---
create
table
Employee(
EmployeeNo
int
primary
key, --员工编号
EmployeeName nvarchar(50) null, --员工名称
CreateUser nvarchar(50) null, --创建人
CreateDate datetimenull, --创建时间
)
执行后结果：
那么假如我们要批量插入10000条数据，应该怎么办？
这里有四种方法（普通循环，事务循环、批量插入、cte插入）
1、普通循环插入（while）
/*
******************************************
***普通循环(插入数据10000，执行时间：1283毫秒)
*******************************************
*/
--
开启开关（记录sql语句各个阶段所消耗的时间）---
set
statistics time on;
--声明两个变量--- declare@Indexint;
declare@Timerdatetime;
--对两个变量进行赋值----set@Index=1;
set@Timer=GETDATE();
--当循环小于1000次执行添加语句---while@Index<=10000begin--执行添加的语句--insertinto Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)
values(@Index,‘员工‘+cast(@IndexasCHAR(5)),‘system‘,GETDATE())
--设置循环次数加1set@Index=@Index+1end--获取执行的毫秒数--selectDATEDIFF(MS,@Timer,GETDATE()) as‘执行时间（毫秒）‘--关闭开关（记录SQL语句各阶段所消耗的时间）setstatistics time off;
执行普通循环插入10000条数据，大概需要1200多毫秒，结果如图所示
2、事务循环插入
/*
******************************************
***事务循环(插入数据1000，执行时间：460毫秒)
*******************************************
*/
--
开启事务--
begin
tran
;
--
开启开关（记录sql语句各个阶段所消耗的时间）---
set
statistics time on;
--声明两个变量---declare@Indexint;
declare@TimerDatetime;
--对两个变量进行赋值----set@Index=1;
set@Timer=GETDATE();
--当循环小于1000次执行添加语句---while@Index<=10000begin--执行添加的语句--insertinto Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)
values(@Index,‘员工‘+cast(@IndexasCHAR(5)),‘system‘,GETDATE())
--设置循环次数加1set@Index=@Index+1end--获取执行的毫秒数--selectDATEDIFF(MS,@Timer,GETDATE()) as‘执行时间（毫秒）‘setstatistics time off;
--提交事务--commit;
执行事务循环插入10000条数据，大概需要400多毫秒，结果如下所示：
3、批量插入
/*
******************************************
***批量插入(插入数据10000，执行时间：33毫秒)
*******************************************
*/
--
开启开关（记录sql语句各个阶段所消耗的时间）--
set
statistics time on;
--声明一个时间变量---declare@Timerdatetime;
---对时间变量进行赋值---set@Timer=GETDATE();
---执行批量操作的sql语句---insert Employee(EmployeeNo,EmployeeName,CreateUser,CreateDate)
selecttop(10000) EmployeeNo=ROW_NUMBER() over( orderby c1.[object_id]),‘员工‘,‘system‘,GETDATE()
from sys.columns as c1 crossjoin sys.columns as c2
orderby c1.object_id--获取执行的毫秒数--selectDATEDIFF(MS, @Timer, GETDATE()) AS[执行时间(毫秒)];
--关闭开关（记录SQL语句各阶段所消耗的时间）--SETSTATISTICS TIME OFF;
执行批量插入10000条数据，大概只要33毫秒，结果如图所示：
4、CTE插入
--
/*******************************************
--
***CTE插入(插入数据10000，执行时间：40毫秒)
--
********************************************/
--
开启开关（记录sql语句各个阶段所消耗的时间）--
set
statistics time on;
--声明一个时间变量并赋值--declare@Timerdatetime=GETDATE();
---将要添加10000条语句组合成CTE模块---
;with CTE(EmployeeNo,EmployeeName,CreateUser,CreateDate) as (
selecttop(10000) EmployeeNo = ROW_NUMBER() over (orderby C1.[OBJECT_ID]), ‘员工‘, ‘system‘, GETDATE()
from SYS.COLUMNS as C1 crossjoin SYS.COLUMNS as C2
orderby C1.[OBJECT_ID]
)
--执行CTE插入语句---insert Employee select EmployeeNo,EmployeeName,CreateUser,CreateDate from CTE;
--获取执行的毫秒数--selectDATEDIFF(MS, @Timer, GETDATE()) as[执行时间(毫秒)];
---关闭开关（记录sql语句各个阶段所消耗的时间）---setstatistics time off;
执行插入10000条数据，大概需要40毫秒，结果如图所示：
最后我们查看一下，批量插入10000条数据的员工表
小结：
1）按执行时间，效率依次为：CTE和批量插入效率相当，速度最快，事务插入次之，单循环插入速度最慢；
2）单循环插入速度最慢是由于INSERT每次都有日志，事务插入大大减少了写入日志次数，批量插入只有一次日志，CTE的基础是CLR，善用速度是最快的
那么，假如我们要批量删除我们插入的数据，怎么办呢？
批量删除有3中方法（循环删除、批量删除、truncate 删除）
1、循环删除
--
/*******************************************
--
***循环删除(删除数据10000，执行时间：20毫秒)
--
********************************************/
set
statistics time on;
--声明一个时间变量---declare@Timerdatetime=GETDATE();
--删除语句--deletefrom Employee
--获取执行的毫秒数--selectDATEDIFF(MS, @Timer, GETDATE()) as[执行时间(毫秒)];
setstatistics time off;
删除10000条数据，所需的时间大概为20毫秒，如下所示：
2、批量删除
/*
******************************************
***批量删除(删除数据10000，执行时间：23毫秒)
*******************************************
*/
set
statistics time on;
declare@Timerdatetime=GETDATE();
SETROWCOUNT10000;
while1=1begin--开启事务--begintran--执行删除--deletefrom Employee;
--提交事务--commit;
IF@@ROWCOUNT=0break;
endsetROWCOUNT0;
--获取执行的毫秒数---selectDATEDIFF(MS, @Timer, GETDATE()) as[执行时间(毫秒)];
setstatistics time off;
删除10000条数据，所需的时间大概为23毫秒，如下所示：
3、truncate删除
--
/*******************************************
--
***truncate删除(删除数据10000，执行时间：3毫秒)
--
********************************************/
set
statistics time on;
--声明一个时间变量--declare@Timerdatetime=getdate();
--执行truncate语句--truncatetable Employee
---获取执行的毫秒数---selectDATEDIFF(MS, @Timer, GETDATE()) as[执行时间(毫秒)]setstatistics 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
原文：http://www.cnblogs.com/xielong/p/7054927.html
内容总结
以上是互联网集市为您收集整理的sql server中的大数据的批量操作（批量插入，批量删除）全部内容，希望文章能够帮你解决sql server中的大数据的批量操作（批量插入，批量删除）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。