如何使用BULK Insert批量1000条将EmployeeDB数据归档至ArchiveEmployeeDB

针对你归档EmployeeDB到ArchiveEmployeeDB时因数据量过大导致的性能问题，以下是几种更优的实现方案，包括你要求的分批插入（每次1000条）和BULK INSERT方案，同时附上原代码的错误修正。

原代码问题修正 #

首先注意原代码里的拼写错误：

• [EmployyeDB] 应为 [EmployeeDB]
• [EmaployeeTable] 应为 [EmployeeTable]

原方案一次性处理所有符合条件的数据，不仅会引发锁表、事务日志暴涨的问题，ID NOT IN的子查询在大数据量下查询效率也极低。

方案一：分批循环INSERT（每次1000条） #

这是最容易落地的优化方式，通过循环分批提取未归档数据，每次插入1000条，减少单次操作的资源占用：

DECLARE @BatchSize INT = 1000;
DECLARE @CurrentDate DATETIMEOFFSET(7) = SYSDATETIMEOFFSET();

-- 临时表存储待归档ID（排除已归档的），加主键提升关联效率
CREATE TABLE #QueueVariable (ID INT PRIMARY KEY);
INSERT INTO #QueueVariable
SELECT ID 
FROM [EmployeeDB].[dbo].[EmployeeTable] 
WHERE CreateDate < DATEADD(MONTH, -6, @CurrentDate)
AND ID NOT IN (SELECT ID FROM [EmployeeArchive].[dbo].[EmployeeTable]);

WHILE EXISTS(SELECT 1 FROM #QueueVariable)
BEGIN
    SET IDENTITY_INSERT [EmployeeArchive].[dbo].[EmployeeTable] ON;

    -- 取TOP 1000条插入归档表
    INSERT INTO [EmployeeArchive].[dbo].[EmployeeTable]
    ([ID], [CreateDate], [CreateLogin], [CreateUser], [UpdateDate], [UpdateLogin], [UpdateUser])
    SELECT TOP (@BatchSize) et.[ID], et.[CreateDate], et.[CreateLogin], et.[CreateUser], et.[UpdateDate], et.[UpdateLogin], et.[UpdateUser]
    FROM [EmployeeDB].[dbo].[EmployeeTable] et
    JOIN #QueueVariable qv ON et.ID = qv.ID;

    SET IDENTITY_INSERT [EmployeeArchive].[dbo].[EmployeeTable] OFF;

    -- 删除已处理的ID，避免重复操作
    DELETE TOP (@BatchSize) FROM #QueueVariable;

    -- 可选：如果开启了显式事务，此处提交以控制事务日志大小
    -- COMMIT TRANSACTION;
END

DROP TABLE #QueueVariable;

额外性能优化点 #

• 归档前可禁用归档表的非聚集索引，插入完成后重建，减少索引维护开销
• 如果数据库是简单恢复模式，可在每批操作后收缩事务日志

方案二：使用BULK INSERT（超大数据量场景） #

BULK INSERT是SQL Server中高效的批量导入工具，适合处理百万级以上的数据，需先将待归档数据导出到文件，再批量导入：

步骤1：导出待归档数据到CSV文件 #

DECLARE @ExportPath NVARCHAR(500) = 'D:\ArchiveData\EmployeeArchive.csv';
DECLARE @CurrentDate NVARCHAR(50) = CONVERT(NVARCHAR(50), SYSDATETIMEOFFSET());

-- 启用xp_cmdshell（默认禁用，需先开启）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

-- 使用bcp命令导出数据
EXEC xp_cmdshell 'bcp "SELECT ID,CreateDate,CreateLogin,CreateUser,UpdateDate,UpdateLogin,UpdateUser FROM EmployeeDB.dbo.EmployeeTable WHERE CreateDate < DATEADD(MONTH, -6, ''' + @CurrentDate + ''') AND ID NOT IN (SELECT ID FROM EmployeeArchive.dbo.EmployeeTable)" queryout "' + @ExportPath + '" -c -t, -S YOUR_SERVER_NAME -T';

步骤2：BULK INSERT导入归档表 #

SET IDENTITY_INSERT [EmployeeArchive].[dbo].[EmployeeTable] ON;

BULK INSERT [EmployeeArchive].[dbo].[EmployeeTable]
FROM 'D:\ArchiveData\EmployeeArchive.csv'
WITH (
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 1, -- 如果导出文件包含表头则设为2
    TABLOCK -- 启用表锁，大幅提升导入速度
);

SET IDENTITY_INSERT [EmployeeArchive].[dbo].[EmployeeTable] OFF;

注意事项 #

• 需确保SQL Server服务账号有文件读写权限
• 操作完成后可关闭xp_cmdshell：EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;

方案三：分区表切换（极致性能，超大规模数据） #

如果你的源表和归档表都是分区表，可以通过分区切换直接将旧数据移动到归档表，这是几乎零开销的瞬间操作：

前提条件 #

• 源表和归档表结构完全一致（包括索引、约束、分区函数）
• 归档表需提前创建对应分区，分区范围匹配待归档数据的CreateDate区间

-- 将源表中CreateDate早于6个月的分区切换到归档表
ALTER TABLE [EmployeeDB].[dbo].[EmployeeTable]
SWITCH PARTITION $PARTITION.PF_EmployeeCreateDate(DATEADD(MONTH, -6, SYSDATETIMEOFFSET()))
TO [EmployeeArchive].[dbo].[EmployeeTable]
PARTITION $PARTITION.PF_EmployeeCreateDate(DATEADD(MONTH, -6, SYSDATETIMEOFFSET()));

这种方式适合数据量极大的场景，完全避免了数据复制，仅修改元数据即可完成归档。

内容的提问来源于stack exchange，提问作者Srikanth