mssql如何快速插入大级别数据

最新推荐文章于 2024-06-03 07:47:47 发布

amwsffkhtd

最新推荐文章于 2024-06-03 07:47:47 发布

阅读量501

点赞数

分类专栏： sqlServer 文章标签： sql

sqlServer 专栏收录该内容

10 篇文章

订阅专栏

本文介绍了SQLServer2012中通过SetNoCountON与Transaction加速批量数据插入的方法，同时展示了使用CTE进行高效插入的实例。通过对比分析，说明了CTE在处理大量数据时的优越性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SQL Server 2012中快速插入批量数据的示例及疑惑

本文大部分内容系转载，点击上面标题去到原文

提供两个方法实现插入大级别数据（1.使用Set NoCount ON+Transaction，2.推荐使用CTE）

1.加上Set NoCount ON，并尽可能加上Transaction

TRUNCATE table Customers
GO

----清除干扰查询
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

 

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();
set nocount on   -----我是set nocount on
declare @i int=1
BEGIN TRANSACTION   -----我是TRANSACTION
while @i<=10000
begin
INSERT INTO Customers (CustomerNumber, CustomerName,
CustomerCity)
SELECT REPLACE(STR(@i, 4), ' ', '0'),'Customer ' + STR(@i,6),
CHAR(65 + (@i % 26)) + '-City'
set @i=@i+1
end
COMMIT
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

Set NoCount On的作用：使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。这在批量插入时将显著提高性能

2.推荐使用CTE

本人试过用递归CTE插入1千万数据，大概花了15分钟左右，效率还是相当可以的。

TRUNCATE table Customers
GO

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO

DECLARE @d Datetime
SET @d=getdate();


WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),
cast('Customer ' + STR(num,6) AS NVARCHAR(50)),
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 10000
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

SET STATISTICS IO OFF ;
SET STATISTICS TIME OFF;
GO

如果你觉得上面的递归CTE看着有点乱，下面给你提供它的缩减版

WITH Seq (num) AS
(SELECT 1
UNION ALL
SELECT num + 1

FROM Seq
WHERE num <= 10000
)
select * from seq
OPTION (MAXRECURSION 0)