首先我们建立一个测试用员工表
---创建一个测试的员工表---
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