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