17370845950

最有效率的SQL Server批量插入方法是使用单个INSERT INTO语句配合多VALUES子句或SELECT与UNION ALL组合，核心优势在于减少数据库交互次数，降低网络开销、事务日志写入、查询优化器负担及锁争用。相比循环单条插入，批量插入显著提升性能，尤其适用于中等数据量场景。对于大量数据，推荐BULK INSERT、OPENROWSET(BULK)或应用程序层面的SqlBulkCopy等更高效方案，同时建议分批处理以平衡性能与资源消耗，避免单次操作过大导致内存压力或长事务风险。

SQL Server插入多行数据，最有效率且常用的方法，无非是利用单个

INSERT INTO

语句配合多个

VALUES

子句，或者通过

SELECT

语句与

UNION ALL

组合来一次性提交。这不仅能显著提升性能，还能简化代码逻辑，避免不必要的资源消耗。

解决方案： 在SQL Server里，批量插入数据其实有几种不同的思路，但核心都是减少与数据库的交互次数。

最直观，也是我日常工作中用得最多的，就是

INSERT INTO ... VALUES

语法。它允许你在一个

INSERT

语句里指定多组要插入的值：

INSERT INTO YourTableName (Column1, Column2, Column3)
VALUES
    ('Value1A', 'Value1B', 'Value1C'),
    ('Value2A', 'Value2B', 'Value2C'),
    ('Value3A', 'Value3B', 'Value3C');
-- ... 更多行

这种方式的好处是显而易见的：一次性打包多行数据，减少了网络往返开销，也让SQL Server的查询优化器有机会一次性处理更多数据。我记得有一次项目，因为数据量不大，但插入频率很高，最初用循环单条插入，数据库CPU一直居高不下，改用这种批量插入后，瞬间就稳定下来了。

另一种常见的做法，尤其当你的数据来源本身就是通过

SELECT

语句生成，或者需要从不同的“虚拟表”中组合数据时，可以使用

INSERT INTO ... SELECT ... UNION ALL

：

INSERT INTO YourTableName (Column1, Column2, Column3)
SELECT 'Value1A', 'Value1B', 'Value1C'
UNION ALL
SELECT 'Value2A', 'Value2B', 'Value2C'
UNION ALL
SELECT 'Value3A', 'Value3B', 'Value3C';
-- ... 更多行

这个方法在处理一些临时计算结果或者需要将多个小数据集合并插入时特别有用。比如，我曾经需要将几个不同报表的数据汇总到一个分析表中，每个报表的数据结构略有差异，但最终要插入的目标表结构是统一的，这时候

UNION ALL

就成了我的得力助手。它本质上是构建了一个临时的结果集，然后一次性插入。

当然，如果数据量特别大，比如几十万、上百万甚至千万级别，那可能就需要考虑更高级的方案了，比如

BULK INSERT

或者应用程序层面的

SqlBulkCopy

，但这通常超出了“怎么写”这种简单T-SQL语句的范畴了。

为什么不推荐循环单条插入？批量插入有哪些性能优势？

说实话，我见过不少新手或者在性能优化上没太多经验的开发者，会习惯性地写一个循环，每次循环里执行一条

INSERT

语句。这在数据量小到可以忽略不计的情况下，可能感觉不出什么异样。但只要数据量稍大一点，或者并发量一上来，数据库的性能瓶颈很快就会显现出来。

不推荐循环单条插入，核心原因在于每次

INSERT

操作都不是孤立的：

• 网络往返开销（Network Round Trips）：每次执行SQL语句，客户端都需要与SQL Server建立连接、发送请求、等待响应。插入1000条数据，单条插入意味着1000次这样的往返，而批量插入可能只需要一次或几次。这个开销在网络延迟高的情况下尤其明显。
• 事务日志写入（Transaction Log Writes）：SQL Server的每个DML操作（包括

  INSERT

  ）都会被记录到事务日志中。单条插入会产生更多的日志记录开销和磁盘I/O。批量插入在单个事务内处理多行，日志记录效率更高。
• 查询优化器开销（Query Optimizer Overhead）：每次执行SQL语句，SQL Server的查询优化器都需要分析语句、生成执行计划。即使是简单的

  INSERT

  ，这个过程也会消耗CPU。批量插入允许优化器为多行数据生成一个更高效的执行计划，减少了重复的计划生成工作。
• 锁和闩锁（Locks and Latches）：频繁的单条插入，可能会导致更多的锁竞争，尤其是在高并发环境下。批量插入可以在一个更长的锁持有时间内完成更多工作，反而可能减少整体的锁争用，因为它减少了锁获取和释放的频率。

所以，批量插入的性能优势就是对症下药，它通过减少上述这些“小动作”的累积开销，显著提升了数据吞吐量。简单来说，就是把零散的体力活，打包成一次性的大活，效率自然就高了。

使用VALUES子句批量插入时，有没有数量限制或最佳实践？

这个问题问得很好，也是我当初在实践中经常会纠结的地方。理论上，

VALUES

子句可以包含很多行，但实际上，我们不能无限地塞入。

SQL Server并没有一个硬性的“你只能插入X行”的限制，但它对单个批次（Batch）的SQL语句有一些限制，比如批处理大小（Batch Size）和参数数量。对于

INSERT INTO ... VALUES

这种形式，虽然你没有显式地使用参数，但每一组值在内部处理时也会有类似的考量。

我个人和行业里普遍的经验是，单次

INSERT INTO ... VALUES

语句中包含的行数，最好控制在几百到一千行之间。超过这个数量，可能会遇到一些问题：

• 语句长度限制：虽然现代SQL Server版本对语句长度的限制已经非常宽松，但过长的SQL字符串本身在传输、解析和优化时都会带来额外的负担。
• 内存消耗：SQL Server在处理一个非常大的SQL语句时，需要在内存中构建其执行计划。如果语句过长，可能会消耗更多的内存。
• 可读性和维护性：一个包含几千甚至上万行

  VALUES

  的SQL语句，简直是噩梦。光是滚动条拉到底，眼睛都花了，更别说调试和修改了。
• 事务日志和锁粒度：虽然批量插入能减少日志开销，但一个超大的批量操作，如果中途失败，回滚的代价也会很大。而且长时间持有锁，在高并发环境下依然可能成为瓶颈。

所以，我的最佳实践通常是：

1. 分批处理（Batching）：如果我有10万行数据要插入，我不会尝试一次性用一个

   INSERT ... VALUES

   搞定。我会将这10万行数据分成100个批次，每个批次1000行，然后循环执行100次

   INSERT

   语句。这样既享受了批量插入的性能优势，又避免了单次操作过大带来的风险。
2. 考虑事务：如果分批处理，每批次可以作为一个独立的事务提交，或者将多个批次包裹在一个更大的显式事务中。这取决于你的业务需求和对数据一致性的要求。我倾向于每个小批次在自己的事务中，这样即使某个批次失败，影响也相对较小。
3. 使用应用程序层面的工具：如果数据量真的非常大，比如从文件导入，或者从另一个系统同步，那么像.NET的

   SqlBulkCopy

   或者Java的JDBC

   addBatch()

   配合

   executeBatch()

   方法会是更好的选择。它们在底层做了很多优化，能够更高效地处理大量数据。

除了VALUES和UNION ALL，还有哪些高效的批量插入策略？

当数据量达到一定规模，或者数据来源不是简单的T-SQL字面量时，我们确实需要一些更“重型”的武器。这些策略通常用于处理外部文件数据导入、跨数据库数据迁移或应用程序层面的高性能插入。

1. BULK INSERT

   命令： 这是SQL Server内置的一个非常强大的命令，用于从操作系统文件（例如CSV、TXT）中高效地导入数据到数据库表中。它的优势在于直接绕过SQL Server的查询处理器，以最小的日志记录方式（取决于恢复模式）将数据加载到表中。

   BULK INSERT YourTableName
   FROM 'C:\YourData\data.csv'
   WITH
   (
       FIELDTERMINATOR = ',',  -- 字段分隔符
       ROWTERMINATOR = '\n',   -- 行分隔符
       FIRSTROW = 2,           -- 如果文件有标题行，从第二行开始
       TABLOCK                 -- 锁定表以提高性能，但会阻塞其他操作
   );

   我用这个命令处理过不少日志文件或外部系统导出的数据，效率极高。但缺点是，它要求数据必须是文件形式，且格式要相对规整。

2. OPENROWSET(BULK...)

   函数： 与

   BULK INSERT

   类似，但它允许你在

   SELECT

   语句中将文件内容作为行集（Rowset）来查询，然后通过

   INSERT INTO ... SELECT ...

   的方式插入。这提供了更大的灵活性，你可以在插入前对数据进行转换、过滤或与其他表进行联接。

   INSERT INTO YourTableName (Column1, Column2, Column3)
   SELECT T.Col1, T.Col2, T.Col3
   FROM OPENROWSET(BULK 'C:\YourData\data.csv',
                   FORMATFILE = 'C:\YourData\format_file.xml') AS T;

   FORMATFILE

   是一个XML或非XML文件，它定义了源文件的结构和列映射，这对于处理复杂格式的文件非常有用。我个人觉得这个比纯

   BULK INSERT

   更灵活一些，因为可以在SQL语句里做更多的事情。

3. 应用程序层面的

   SqlBulkCopy

   (for .NET)： 如果你在用.NET开发应用程序，

   SqlBulkCopy

   类是进行高性能批量数据插入的首选