如何在主键由序列生成时使用SqlBulkCopy批量插入数据

这个问题的核心是SqlBulkCopy默认会尝试把DataTable的所有列（包括Id）写入目标表，但你没给Id赋值，而目标表的Id列不允许Null；直接移除Id列又会因为列序号不匹配报错。下面给你两种可行的解决方案：

方案一：仅映射需要插入的列，让数据库自动使用序列生成Id #

这是最简洁的方法，不需要手动处理Id，让数据库的默认约束直接生效。你可以通过两种方式实现：

方式1：创建只包含待插入列的DataTable #

修改初始化DataTable的SQL，只查询需要插入的列而非*，这样DataTable里就不会包含Id列：

var table = new DataTable();
// 只选择需要插入的列，排除Id
using (var adapter = new SqlDataAdapter($"SELECT TOP 0 SomeColumn, AnotherColumn FROM dbo.MyTable", conn)) 
{ 
    adapter.Fill(table); 
}
foreach (Data d in data) {
    var row = table.NewRow();
    row["SomeColumn"] = d.someColumnValue;
    row["AnotherColumn"] = d.anotherColumnValue;
    table.Rows.Add(row);
}
using (var bulk = new SqlBulkCopy(conn)) {
    bulk.DestinationTableName = "dbo.MyTable";
    // DataTable的列和目标表的非Id列完全对应，直接写入即可
    bulk.WriteToServer(table);
}

此时SqlBulkCopy会忽略目标表的Id列，数据库会自动调用序列的NEXT VALUE填充Id值。

方式2：显式设置列映射（适合DataTable必须包含Id列的场景） #

如果因为业务需求不能修改初始化DataTable的SQL，那可以显式指定SqlBulkCopy的列映射，只处理需要插入的列：

var table = new DataTable();
using (var adapter = new SqlDataAdapter($"SELECT TOP 0 * FROM dbo.MyTable", conn)) { 
    adapter.Fill(table); 
}
foreach (Data d in data) {
    var row = table.NewRow();
    row["SomeColumn"] = d.someColumnValue;
    row["AnotherColumn"] = d.anotherColumnValue;
    // 无需给Id赋值，后续会跳过该列
    table.Rows.Add(row);
}
using (var bulk = new SqlBulkCopy(conn)) {
    bulk.DestinationTableName = "dbo.MyTable";
    // 显式添加需要映射的列，排除Id
    bulk.ColumnMappings.Add("SomeColumn", "SomeColumn");
    bulk.ColumnMappings.Add("AnotherColumn", "AnotherColumn");
    bulk.WriteToServer(table);
}

通过ColumnMappings告诉SqlBulkCopy只处理指定列，Id列会被忽略，数据库自动用序列生成值。

方案二：提前批量获取序列值，赋值给DataTable的Id列 #

如果你需要提前知道插入后的Id值，可以先从数据库批量获取一批序列值，再赋值给DataTable的每行：

// 第一步：批量获取对应数量的序列值
var idList = new List<int>();
var batchSize = data.Count;
using (var cmd = new SqlCommand($"SELECT NEXT VALUE FOR dbo.MyTableId FROM (VALUES(1)) AS x(n) CROSS JOIN (SELECT TOP {batchSize} 1 FROM sys.all_columns) AS y", conn))
{
    conn.Open();
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            idList.Add(reader.GetInt32(0));
        }
    }
    conn.Close();
}

// 第二步：填充DataTable并赋值Id
var table = new DataTable();
using (var adapter = new SqlDataAdapter($"SELECT TOP 0 * FROM dbo.MyTable", conn)) { 
    adapter.Fill(table); 
}
for (int i = 0; i < data.Count; i++)
{
    var d = data[i];
    var row = table.NewRow();
    row["Id"] = idList[i];
    row["SomeColumn"] = d.someColumnValue;
    row["AnotherColumn"] = d.anotherColumnValue;
    table.Rows.Add(row);
}

// 第三步：执行批量插入
using (var bulk = new SqlBulkCopy(conn)) {
    bulk.DestinationTableName = "dbo.MyTable";
    bulk.WriteToServer(table);
}

这里用CROSS JOIN一次性生成对应数量的序列值，确保值符合序列规则，再赋值给每行Id列后插入。

为什么你之前的方法不行？ #

• 移除Id列但未设置列映射：SqlBulkCopy默认按列的序号匹配而非列名，DataTable少了Id列后，会把第一列（SomeColumn）对应到目标表的第一列（Id），类型不匹配导致报错。
• 设置AutoIncrement = true：这是客户端生成的自增值，和数据库序列完全无关，重复运行时会从0开始，极易和数据库已有的Id冲突。

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