批量插入SQLServer数据：简单Insert与表值参数方法

在SQL Server中，批量插入大量数据是常见的操作，特别是在数据分析、数据迁移或系统初始化时。本文将介绍两种不同的方法来实现高效的批量插入：标准的INSERT语句和使用表值参数（Table-Valued Parameters，TVP）。 首先，我们创建一个测试环境，包括数据库和表。通过执行以下代码片段，我们可以建立一个名为`BulkTestDB`的数据库，并在其中创建两个表：`BulkTestTable`和`BulkUdt`。`BulkTestTable`用于存储用户信息，包括整数ID、32字符的用户名和16字符的密码；而`BulkUdt`是一个表类型，它定义了与`BulkTestTable`相同的结构，但作为参数传递时更为高效。 ```sql -- 创建数据库 CREATE DATABASE BulkTestDB; GO -- 使用数据库 USE BulkTestDB; GO -- 创建用户表 CREATE TABLE BulkTestTable ( Id INT PRIMARY KEY, UserName nvarchar(32), Pwd varchar(16) ); -- 创建表值参数 CREATE TYPE BulkUdt AS TABLE ( Id INT, UserName nvarchar(32), Pwd varchar(16) ); ``` 标准的批量插入方法是使用循环和参数化的`INSERT`语句。例如，以下代码展示了如何插入100万条数据，每批插入10万条，共进行10次循环： ```sql DECLARE @sw Stopwatch SET @sw = NEW Stopwatch() -- 连接到数据库 DECLARE @sqlConn SqlConnection SET @sqlConn = NEW SqlConnection(ConfigurationManager.ConnectionStrings['ConnStr'].ConnectionString) -- 定义命令对象 DECLARE @sqlComm SqlCommand SET @sqlComm = NEW SqlCommand() SET @sqlComm.CommandText = 'INSERT INTO BulkTestTable (Id, UserName, Pwd) VALUES (@p0, @p1, @p2)' -- 参数设置 SET @sqlComm.Parameters.AddWithValue('@p0', SqlDbType.Int) SET @sqlComm.Parameters.AddWithValue('@p1', SqlDbType.NVarChar) SET @sqlComm.Parameters.AddWithValue('@p2', SqlDbType.VarChar) SET @sqlComm.CommandType = CommandType.Text SET @sqlComm.Connection = @sqlConn BEGIN TRY -- 开始计时 SET @sw.Start() -- 循环插入数据 FOR multiply FROM 0 TO 9 BEGIN FOR count FROM multiply * 100000 TO (multiply + 1) * 100000 BEGIN SET @sqlComm.Parameters.AddWithValue('@p0', count) SET @sqlComm.Parameters.AddWithValue('@p1', 'User' + CAST(count AS varchar(10))) SET @sqlComm.Parameters.AddWithValue('@p2', 'Pwd' + CAST(count AS varchar(10))) EXECUTE @sqlComm END END -- 记录插入时间 SET @sw.Stop() END TRY BEGIN CATCH -- 错误处理 PRINT 'Error: ' + ERROR_MESSAGE() END CATCH -- 关闭连接 CLOSE @sqlConn DEALLOCATE @sqlComm ``` 然而，当处理大量数据时，这种方式可能会受到性能限制。为了解决这个问题，我们可以利用表值参数。通过将数据作为一个表值集传递给存储过程，可以显著提高插入速度。以下是使用TVP的方法： ```sql -- 假设数据源是一个临时表或已经存在的表 DECLARE @BulkData TABLE ( Id INT, UserName nvarchar(32), Pwd varchar(16) ) INSERT INTO @BulkData VALUES ... -- 存储过程接收表值参数 CREATE PROCEDURE InsertBulkUsingTVP (@BulkData BulkUdt READONLY) AS BEGIN INSERT INTO BulkTestTable SELECT * FROM @BulkData END -- 调用存储过程 EXEC InsertBulkUsingTVP @BulkData ``` 通过使用表值参数，SQL Server可以在一次操作中处理整个数据集，避免了多次单行插入的开销，从而提高性能。然而，TVP适用于数据已经预处理好的情况，如果数据需要在程序中动态生成，可能需要先临时存储，再调用存储过程。 总结来说，批量插入数据时，可以选择标准的INSERT语句配合循环，适合固定数量的数据插入，或者使用表值参数，适用于大规模数据或预处理的数据集，能有效提升性能。根据具体场景选择合适的方法是关键。