兄弟，太懂你处理这种超大批量数据插入的痛苦了——40万条180列的管道分隔数据，单条INSERT那速度简直能让人盯着进度条发呆到崩溃，改成1000条批量动态SQL本来以为能起飞，结果又掉坑里了对吧？结合我踩过的各种坑，给你一套从最优到备选的解决方案：

1. 首选：用BULK INSERT直接拉满性能 #

这绝对是处理这种场景的天花板级方案，SQL Server原生的批量加载工具，专门为大文件导入设计，比任何动态SQL拼接都高效N倍。

操作步骤： #

先确认你的文本文件列顺序、数据类型和目标表完全匹配，然后执行下面的语句就行：

BULK INSERT YourTargetTable
FROM 'C:\YourDataFile\large_data.txt'
WITH (
    FIELDTERMINATOR = '|',          -- 指定分隔符是管道符
    ROWTERMINATOR = '\n',           -- 行分隔符，要是你的文件是Windows格式就改成'\r\n'
    FIRSTROW = 2,                   -- 有表头的话跳过第一行，没有就去掉这个参数
    TABLOCK,                        -- 启用表锁，大幅降低写入时的锁竞争
    BATCHSIZE = 10000,              -- 每批处理1万条，可根据服务器内存调整（5000-20000都可以测）
    CODEPAGE = '65001'              -- 如果文件是UTF-8编码，加上这个参数避免乱码
);

如果你的数据里有特殊情况（比如字段值本身包含管道符），可以提前处理数据，或者用格式文件（.fmt）来精确映射每一列，不过绝大多数纯管道分隔的场景用上面的参数就够了。

2. 备选：优化动态SQL批量插入（必须用代码处理数据时） #

如果因为权限限制、需要提前做字段校验/转换，没法用BULK INSERT，那得把动态SQL的坑填上：

常见问题&解决办法： #

• 动态SQL字符串超限：180列×1000条拼出来的SQL可能超长？别用VARCHAR拼接，一定要用NVARCHAR(MAX)，它能存2GB内容，完全够你造。
• 数据转义/类型不匹配报错：字符串里的单引号没转义、日期格式不对直接导致SQL执行炸锅。解决办法：
  • 字符串字段统一把单引号'替换成两个单引号''
  • 日期类型转成'YYYY-MM-DD HH:MI:SS'这种SQL Server认的标准格式
• 批量大小不合理：1000条可能不是最优值，建议测试5000-20000条的批量，找到服务器性能平衡点（太大占内存，太小失去批量优势）

优化后的动态SQL示例思路： #

先把文本文件读到临时表（用OPENROWSET或者SSIS都可以），再批量拼接插入：

DECLARE @SQL NVARCHAR(MAX) = 'INSERT INTO YourTargetTable (Col1, Col2, ..., Col180) VALUES ';
DECLARE @BatchSize INT = 5000;
DECLARE @CurrentRow INT = 1;

-- 假设数据已经读到临时表#TempData，带自增RowNum列
WHILE @CurrentRow <= (SELECT COUNT(*) FROM #TempData)
BEGIN
    -- 用STRING_AGG拼接VALUES部分，比循环拼高效太多
    SET @SQL = @SQL + (
        SELECT STRING_AGG(
            CONCAT('(', 
                QUOTENAME(REPLACE(Col1, '''', ''''''), ''''), ',',  -- 字符串转义
                Col2, ',',  -- 数值字段直接拼
                QUOTENAME(CONVERT(VARCHAR(20), Col3, 120), ''''),  -- 日期转标准格式
                ...,  -- 剩下177列依次处理
                ')'),
            ','
        )
        FROM #TempData
        WHERE RowNum BETWEEN @CurrentRow AND @CurrentRow + @BatchSize - 1
    );

    EXEC sp_executesql @SQL;
    -- 重置SQL变量，准备下一批
    SET @SQL = 'INSERT INTO YourTargetTable (Col1, Col2, ..., Col180) VALUES ';
    SET @CurrentRow = @CurrentRow + @BatchSize;
END

3. 辅助优化：让插入速度再上一个台阶 #

• 临时禁用非必要索引/约束：插入前禁用目标表的非聚集索引、外键约束，插完再重建/启用——能大幅减少写入时的索引维护开销（注意：主键、唯一约束别禁用，避免插重复数据）
• 事务控制：每10万条左右提交一次事务，避免事务日志暴涨（如果数据库是简单恢复模式，日志压力会小很多）
• 服务器临时调优：临时增大max degree of parallelism、max server memory等参数，提升插入性能（记得插完改回原配置）

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