首页 / 数据库 / SQLServer / sql server 大数据量的insert、delete操作优化

sql server 大数据量的insert、delete操作优化2014-03-14经常使用的语句！

--大批量导出orders表:insert

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

go

SET NOCOUNT ON

BEGIN TRANSACTION

INSERT INTO test.dbo.orders with（tablock） SELECT * FROM

bak.dbo.Orders

WHERE ordertime BETWEEN "2010-05- 01" AND "2010-05-10"

COMMIT

go

--大批量导出orders 表:delete

DBCC DROPCLEANBUFFERS

DBCC FREEPROCCACHE

go

SET NOCOUNT ON

BEGIN TRANSACTION

while 1=1

begin

delete top（1000） from test.dbo.orders with（tablock）

WHERE ordertime BETWEEN "2010-05-01" AND "2010-05-10"

if @@rowcount<1000

break

end

COMMIT

go