使用SqlBulkCopy插入带有自增列的数据

Question

我正在使用SqlBulkCopy对象将几百万个生成的行插入到数据库中。唯一的问题是，我要插入的表有一个标识列。我尝试将SqlBulkCopyOptions设置为SqlBulkCopyOptions.KeepIdentity，并将标识列设置为0、DbNull.Value和null，但都无法解决问题。我觉得我可能错过了什么很简单的东西，如果有人能启示我那就太好了。谢谢！

编辑 为了澄清，我没有在导入的DataTable中设置标识值。我希望它们作为导入的一部分生成。

编辑2 这是我用来创建基本SqlBulkCopy对象的代码。

SqlBulkCopy sbc = GetBulkCopy(SqlBulkCopyOptions.KeepIdentity);
sbc.DestinationTableName = LOOKUP_TABLE;

private static SqlBulkCopy GetBulkCopy(SqlBulkCopyOptions options = 
    SqlBulkCopyOptions.Default) 
{
    Configuration cfg = WebConfigurationManager.OpenWebConfiguration("/RSWifi");
    string connString =
    cfg.ConnectionStrings.ConnectionStrings["WifiData"].ConnectionString;
    return new SqlBulkCopy(connString, options);
}

- FlyingStreudel

---

现在只要有一种方法可以批量获取@@IDENTITY并更新源代码就好了。 - David V. Corbin

Answer 1

如果想要目标表分配身份标识，请勿使用SqlBulkCopyOptions.KeepIdentity选项。相反地，请不要将源中的身份标识映射到目标表，也不要从源中提取它以发送到SqlBulkCopy。

- jason

---

我需要在插入时生成 Identity 值，这有助于我的工作。 - FlyingStreudel

哦，你希望目标分配值？ 从你的问题中一点也不清楚。 不要使用SqlBulkCopyOptions.KeepIdentity！ 关闭它，并且在映射时以及从源头提取时都不要包括它。 - jason

哦，我被文档中的“当未指定时，目标会分配身份值”这句话搞糊涂了。我以为它意味着当没有指定值时才会执行 :( - FlyingStreudel

@FlyingStreudel：没错。在SqlBulkCopyOptions中有很多选项。当你不指定SqlBulkCopyOptions.KeepIdentity选项时，目标表将会分配身份。 - jason

Answer 2

填写BulkCopy对象的ColumnMapping，不要映射标识列。标识列将由目标数据库生成。

- Wim

---

这刚刚解决了一个困扰我数小时的问题。 - Alex

这对我很有帮助，因为我的源/目标列数不同。源数据具有用于以特定格式显示数据的列。这也意味着数据库中有重复的数据。因此，我使用了ColumnMappings集合来跳过源数据中不需要保存在数据库中的某些列。 - iCode

尽管我认为这个答案是针对我的问题的，但我并没有完全理解。如果您不将标识列添加到“DataTable”中，例如returnVal.Columns.Add("Id", typeof(int));，则会出现映射问题。但是，不应该为此列分配值，因此请不要添加dataRow["id"] = ...etc.。 - Liam

Answer 3

您有两个选择 -

1 - 使用KeepIdentity来保留源表的Identity值。

2 - 不映射Identity字段。如果您不尝试分配一个值，则目标表将自动分配一个值。

- JNK

---

不为变量分配值等同于分配 DbNull.Value。我仍然会得到相同的 Column 'Id' does not allow DBNull.Value. IOE :( 或者你是指甚至不要在我的 DataTable 中包括 Id 列？ - FlyingStreudel

@Flying - 你有关闭 KeepIdentity 吗？这个值需要设置为 FALSE 才能让目标赋值。 - JNK

我添加了创建 SqlBulkCopy 对象的代码。我没有看到一个名为 KeepIdentity 的字段/属性可以设置。 - FlyingStreudel

@Flying - 抱歉，请不要将其设置为“TRUE”。如果设置了该值，目标对象将无法自动分配标识。 - JNK

Answer 4

以下是我在.NET中解决此问题的方法（dt是您的数据表）：

dt.Columns.Cast<DataColumn>().ForEach((c, i) => sqlBulkCopy.ColumnMappings.Add(c.ColumnName, i + 1));

你可以通过从1开始而不是0来为目标列分配序数，从而跳过身份（Id）列。最初的回答：您可以通过将目标列的顺序从1开始而不是0来跳过身份（Id）列。

- silkfire

Answer 5

最初的回答

这是一张表格。

CREATE TABLE [dbo].[ProductShippingMethodMap](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [ShippingMethodId] [int] NOT NULL,
    [ParentProductId] [int] NOT NULL,
 CONSTRAINT [PK_ProductShippingMethodMap] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

以下C#代码是可行的。

 DataTable dtQtyData = new DataTable();
        dtQtyData.Clear();
        dtQtyData.Columns.Add("Id", typeof(int));

    dtQtyData.Columns.Add("ProductId", typeof(int));
    dtQtyData.Columns.Add("ShippingMethodId", typeof(int));
    dtQtyData.Columns.Add("ParentProductId", typeof(int));


    for (int i = 0; i < ShippingMethodIds.Length; i++)
    {
        for (int j = 0; j < ProductIds.Length; j++)
        {
            var productId = ProductIds[j];
            var shippingMethodId = ShippingMethodIds[i];
            dtQtyData.Rows.Add(new object[] {0,productId, shippingMethodId, parentProductId });
        }

    }
    var connectionString = new DataSettingsManager().LoadSettings().DataConnectionString;
    SqlBulkCopy bulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.Default);
    bulkcopy.DestinationTableName = "ProductShippingMethodMap";
    bulkcopy.WriteToServer(dtQtyData);

- sina_Islam

---

我一直收到主键重复违规的错误。 - Bercovici Adrian

它不应该像表定义中的 [Id] [int] IDENTITY(1,1) NOT NULL 一样，它是主键并自动递增1。因此，它应该遵守这个规则。 - sina_Islam

Answer 6

是的，您使用 SqlBulkCopyOptions.KeepIdentity 选项是正确的，然后批量复制写入器不会考虑您的表结构，该对象从开始列中编写，因此为了满足我们的需要，我以同样的方式保留标识字段在我的表中，只需在您的数据表对象中添加一个额外的列与您所需的其他列，并将 null 值传递给此列，然后表自动处理标识。

- user2522747

Answer 7

当使用JDBC SQLServerBulkCSVFileRecord结构时，标识列确实需要映射，但是标识列中的值会被忽略。

- Glen Little

Answer 8

在我的情况下，问题出在列名中的空格以及我在 SQL 表格中一个列中错误地使用了破折号（-）而非下划线（_）。我将表格中的空格和破折号替换为下划线，这样问题就解决了。

- binu

Answer 9

原因：Excel表格末尾存在一些空行，看起来可能像是空白行。批量上传程序试图将这些空白行上传到表格中。

解决方案：仅选择包含数据的行 - 将数据复制到新工作表中。假设您的数据在“Sheet 1”中，请将其移动到“Sheet 2”，然后删除“Sheet 1”。

- Vinay K