使用T-SQL批量插入数据的高效方法

在SQL Server中，T-SQL提供了多种方法来批量插入数据。高效地执行批量插入操作不仅可以提升性能，还能减少对数据库资源的消耗。本文将详细介绍几种常见的T-SQL批量插入数据的方法，并深入解析它们的优缺点及适用场景。

1. 使用INSERT INTO ... SELECT语句

这是最常用的一种批量插入方式。通过从一个表或查询结果集中选择数据并插入到另一个表中，可以实现高效的数据迁移和复制。

示例代码：

-- 假设有一个源表 SourceTable 和目标表 DestinationTable
INSERT INTO DestinationTable (Column1, Column2, Column3)
SELECT ColumnA, ColumnB, ColumnC
FROM SourceTable
WHERE SomeCondition = 'Value';

这种方法适用于从现有表中选择数据进行插入的情况，且支持复杂的查询逻辑（如JOIN、子查询等）。

2. 使用BULK INSERT命令

BULK INSERT是SQL Server提供的用于从文件系统中的文件直接加载数据到数据库表的命令。它特别适合处理大量结构化数据文件（如CSV、TXT等）。

示例代码：

BULK INSERT TargetTable
FROM 'C:\Data\sourcefile.csv'
WITH (
    FIELDTERMINATOR = ',', -- 字段分隔符
    ROWTERMINATOR = '\n',  -- 行分隔符
    FIRSTROW = 2           -- 跳过标题行
);

注意事项：

• 需要确保SQL Server服务账户有权访问指定的文件路径。
• 数据文件必须严格符合表结构定义，否则可能引发插入错误。

3. 利用OPENROWSET函数

OPENROWSET允许我们直接从外部数据源读取数据并插入到SQL Server表中。这种方式非常适合处理临时性或一次性任务。

示例代码：

INSERT INTO TargetTable (Column1, Column2)
SELECT *
FROM OPENROWSET('MSDASQL', 
                'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\Data;', 
                'SELECT * FROM sourcefile.csv');

此方法的优点在于无需提前将数据导入到SQL Server实例中即可完成操作，但配置相对复杂。

4. 结合SSIS（SQL Server Integration Services）

对于更复杂的ETL（Extract, Transform, Load）场景，可以使用SSIS包来实现数据的批量插入。虽然这不是纯T-SQL解决方案，但在某些情况下它是最佳选择。

5. 使用INSERT INTO ... VALUES结合动态SQL

当需要插入少量记录时，可以考虑使用INSERT INTO ... VALUES语法。但如果数据量较大，则可以通过构建动态SQL字符串来提高效率。

示例代码：

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += 'INSERT INTO TargetTable (Column1, Column2) VALUES (' + 
               QUOTENAME(Value1, '''') + ', ' + QUOTENAME(Value2, '''') + '); '
FROM SourceData;

EXEC sp_executesql @sql;

尽管这种方法灵活，但对于非常大的数据集来说可能会导致性能问题。

性能优化建议

1. 禁用索引：在大批量插入之前暂时禁用非聚集索引，完成后重新启用以加快插入速度。
2. 批量提交：避免一次性插入所有数据，而是分批提交事务以减少内存占用。
3. 调整锁机制：使用TABLOCK提示可减少锁定开销，例如：

   INSERT INTO TargetTable WITH (TABLOCK) ...
   

4. 预分配空间：为表分配足够的存储空间以避免频繁扩展。

流程图：批量插入操作的主要步骤

graph TD
    A[开始] --> B{选择方法}
    B -->|INSERT INTO ... SELECT| C[从表中选择数据]
    B -->|BULK INSERT| D[从文件加载数据]
    B -->|OPENROWSET| E[连接外部数据源]
    B -->|SSIS| F[使用集成服务]
    C --> G[完成插入]
    D --> G
    E --> G
    F --> G