大量数据插入 sql语句

### SQL一次性插入大量数据的方法 在处理大数据集时，如何高效地将大量数据批量插入数据库是许多开发人员面临的关键挑战之一。传统的单条记录插入方式不仅效率低下，还可能导致数据库性能问题。为了优化这一过程，SQL Server 提供了几种批量插入数据的方法，包括 `BULK` 和表值参数 (Table-Valued Parameters)。 #### 1. 基础环境搭建 我们需要创建一个用于测试的数据库和表。示例中使用的 SQL 脚本如下： ```sql -- 创建数据库 CREATE DATABASE BulkTestDB; GO USE BulkTestDB; GO -- 创建表 CREATE TABLE BulkTestTable ( Id INT PRIMARY KEY, UserName NVARCHAR(32), Pwd VARCHAR(16) ); GO -- 创建表值类型 CREATE TYPE BulkUdt AS TABLE ( Id INT, UserName NVARCHAR(32), Pwd VARCHAR(16) ); ``` 这里我们创建了一个名为 `BulkTestDB` 的数据库，并在其中创建了一个名为 `BulkTestTable` 的表，包含三列：`Id`、`UserName` 和 `Pwd`。此外，还定义了一个用户定义的表类型 `BulkUdt`，用于存储与 `BulkTestTable` 相同的数据结构。 #### 2. 使用简单 Insert 语句插入大量数据 接下来，我们将尝试使用简单的 `INSERT` 语句来插入100万条数据，并观察执行效率。 ```csharp Stopwatch sw = new Stopwatch(); // 定义计时器 using(SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString)) { SqlCommand sqlComm = new SqlCommand(); sqlComm.CommandText = "INSERT INTO BulkTestTable (Id, UserName, Pwd) VALUES (@p0, @p1, @p2)"; sqlComm.Parameters.Add("@p0", SqlDbType.Int); sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar); sqlComm.Parameters.Add("@p2", SqlDbType.VarChar); sqlComm.Connection = sqlConn; sqlConn.Open(); try { for (int multiply = 0; multiply < 10; multiply++) { for (int count = multiply * 100000; count < (multiply + 1) * 100000; count++) { sqlComm.Parameters["@p0"].Value = count; sqlComm.Parameters["@p1"].Value = $"User-{count * multiply}"; sqlComm.Parameters["@p2"].Value = $"Pwd-{count * multiply}"; sw.Start(); sqlComm.ExecuteNonQuery(); sw.Stop(); if (count % 100000 == 0) { Console.WriteLine($"Elapsed Time is {sw.ElapsedMilliseconds} Milliseconds"); } } } } catch (Exception ex) { throw ex; } finally { sqlConn.Close(); } } ``` 从上面的代码可以看到，采用简单的 `INSERT` 语句逐条插入数据时，即使是在插入10万条数据的情况下，耗时也达到了72390毫秒，显然这种方式并不适合大批量数据的插入操作。 #### 3. 使用 BULK 插入 针对大量数据的插入需求，SQL Server 提供了 `BULK` 插入机制，该机制能够在客户端缓存大量数据后，一次性将其导入数据库，极大地提高了插入效率。 ```csharp public static void BulkToDB(DataTable dt) { using(SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString)) { SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn); bulkCopy.DestinationTableName = "BulkTestTable"; sqlConn.Open(); bulkCopy.WriteToServer(dt); sqlConn.Close(); } } ``` 在上面的示例中，我们创建了一个 `SqlBulkCopy` 对象，并指定了目标表名。打开数据库连接后，使用 `WriteToServer` 方法将 `DataTable` 中的数据一次性写入数据库。 #### 总结 - **简单 Insert**：适用于少量数据的插入，对于大量数据的插入效率较低。 - **BULK 插入**：特别适合于大批量数据的快速插入，能够显著提高插入效率。 在实际应用中根据数据量大小选择合适的插入策略是非常重要的。对于大数据量的场景，推荐使用 `BULK` 插入机制来提高数据处理效率。