与SqlBulkCopy的标识列插入 [英] SqlBulkCopy Insert with Identity Column

问题描述

我使用的是 SqlBulkCopy的对象插入几百万行生成到一个数据库。唯一的问题是，我插入表中有标识列。我曾尝试设置 SqlBulkCopyOptions 到 SqlBulkCopyOptions.KeepIdentity 和标识列设置为 0 的 DbNull.Value 和空。其中没有工作过。我觉得我失去了一些东西pretty简单，如果有人可以赐教那将是梦幻般的。谢谢！

的修改的为了澄清，我没有在数据表我导入设置标识值。我希望他们可以作为导入的一部分生成。

的编辑2 的
    这里是code我用它来创建基础 SqlBulkCopy的对象。

  SqlBulkCopy的SBC = GetBulkCopy（SqlBulkCopyOptions.KeepIdentity）;
sbc.DestinationTableName = LOOKUP_TABLE;私有静态SqlBulkCopy的GetBulkCopy（SqlBulkCopyOptions选项=
    SqlBulkCopyOptions.Default）
{
    配置CFG = WebConfigurationManager.OpenWebConfiguration（/ RSWifi）;
    串CONNSTRING =
    cfg.ConnectionStrings.ConnectionStrings [WifiData]的ConnectionString。
    返回新SqlBulkCopy的（CONNSTRING，期权）;
}
 

解决方案

要具有目标表指定的身份，不要使用 SqlBulkCopyOptions.KeepIdentity 选项。相反，不映射从源头上身份，并没有从源头提取它通过发送到 SqlBulkCopy的。

I am using the SqlBulkCopy object to insert a couple million generated rows into a database. The only problem is that the table I am inserting to has an identity column. I have tried setting the SqlBulkCopyOptions to SqlBulkCopyOptions.KeepIdentity and setting the identity column to 0's, DbNull.Value and null. None of which have worked. I feel like I am missing something pretty simple, if someone could enlighten me that would be fantastic. Thanks!

edit To clarify, I do not have the identity values set in the DataTable I am importing. I want them to be generated as part of the import.

edit 2 Here is the code I use to create the base SqlBulkCopy object.

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);
}

解决方案

To have the destination table assign the identity, do not use the SqlBulkCopyOptions.KeepIdentity option. Instead, don't map the identity from the source, and don't extract it from source to send through to SqlBulkCopy.

这篇关于与SqlBulkCopy的标识列插入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！