logo标签列表

如何使用SQLBULKCOPY获取插入到表中的记录ID？

c#sqlsql-server
10
据我所知，你不能这样做。如果你需要这样做，并且仍然需要使用批量插入，你应该先将数据批量插入到一个临时表中，然后再使用INSERT的OUTPUT子句从临时表中插入到真实表中。 - Lasse V. Karlsen
请不要打标记为垃圾邮件，只使用相关语言（对于您而言，SQL和C#）标记您的问题。 - eddie_cat
你使用 SqlBulkCopy 有特定的原因吗？它是否具有您需要的特定功能，还是您仅仅想在单个调用中插入多个记录？ - Solomon Rutzky
@srutzky 我只是想在一次调用中插入几千条记录。 - Feanon
在这种情况下，我的两个答案都适用。如果您需要SqlBulkCopy的特定功能（例如BulkCopyOptions等），那么我关于触发器的第一个答案是合适的。否则，对于多行操作，我的另一个有关表值参数的答案是首选方法（从SQL Server 2008开始）。 - Solomon Rutzky
4个回答
10

不可能。您必须使用传统的INSERT语句，使用scope_identity()或使用OUTPUT子句来获取ID的值-假设ID来自IDENTITY列类型。

SqlBulkCopy不适用于事务性插入。它旨在在服务器之间复制大量数据。

你在这里对问题的本质做出了错误的假设。该问题涉及一次性插入多行。如果 OP 使用批处理等功能，则使用 SqlBulkCopy 是有效的。但是，仍然有比使用 SCOPE_IDENTITY() 的单行插入更好的方法来插入多行。 - Solomon Rutzky
OP已确认他们一次插入数千条记录。这意味着单行插入是完成此任务最慢的方法。虽然SqlBulkCopy可能不是必需的，但说它无效/不正确是不公平的。如果您不需要SqlBulkCopy的任何特殊功能，则将整个数据集作为TVP或至少作为XML传递，并通过OUTPUT子句（我的第二个答案）捕获所有新ID，是批量操作的首选方法。 - Solomon Rutzky
7

如果您正在插入多行，但不需要 SqlBulkCopy 的任何特殊功能，则应考虑使用 Table-Valued Parameters（TVPs）。您可以创建一个接受 TVP 并返回结果集的存储过程。存储过程的主体应该是以下内容：

CREATE PROCEDURE SchemaName.StoredProcName
(
  @Rows   SchemaName.TableTypeName READONLY
)
AS
SET NOCOUNT ON;

INSERT INTO SchemaName.ImportTable (Field1, ..., FieldN)
OUTPUT INSERTED.ID, INSERTED.IdentifyingField1, ..., INSERTED.IdentifyingFieldN
   SELECT Field1, ..., FieldN
   FROM @Rows;

您的应用程序代码将通过SqlCommand.ExecuteReader()调用proc，您将获得新的ID和标识字段作为SqlDataReader返回。
TVPs是在SQL Server 2008中引入的。如果您使用的是SQL Server 2005，则仍可以将一批行作为XML发送。但是，即使作为XML，您仍然可以执行多行插入和输出子句以捕获新的ID，如上所示。
我几年前写了一篇文章，展示了从C#代码调用具有表值参数的存储过程的几个不同选项。您可以在这里找到它：从应用程序流式传输数据到SQL Server 2008，但需要免费注册才能阅读该站点上的文章。
另一个例子可以在这个答案中找到：如何传递表值参数，其中显示了表类型、存储过程（没有输出子句）和C#代码的创建。
0

一种获得ID的方法是通过使用以下方式将其强制插入到您的标识中：

SET IDENTITY_INSERT (Transact-SQL)

use database
go

set identity_insert myDatabase.mySchema.MyTable on
go

...

set identity_insert myDatabase.mySchema.MyTable off
go

虽然这些几乎不适用于客户端代码进程。

此外，我从未尝试过在客户端代码（C＃，VB，任何其他语言）中直接执行此类过程。

如@CamBruce所述，这些是针对批处理或其他用途而设计的。也许您最好使用INSERT ... SELECT ...语句，如您问题中的参考所述：

{{link1：SqlBulkCopy.WriteToServer Method (DataRow[])}}

重要提示：

如果源表和目标表位于同一SQL Server实例中，则使用Transact-SQL INSERT ... SELECT语句复制数据更容易且更快。

感谢您的回答！是否有一种方法可以插入5000条记录的表格和第二个表格的进一步插入，其中一个列是来自上一页的id记录。外键。使用C#编程语言？ - Feanon
当然可以！只需在您的ID上停用自增特性，然后通过常规的ADO.NET启动您的“INSERT ... SELECT”操作即可。 - Will Marcouiller
-1

假设以下两个陈述对您的特定情况是正确的：

  1. 输入数据中至少有一个，如果不是更多的字段可以唯一地标识数据（即，如果您不使用IDENTITY字段，则可以将它们用作PK）。
  2. 此应用程序代码调用SqlBulkCopy的过程是单线程的（即，一次只能运行一个导入过程）。

那么就有一个选项。正如其他人所述，SqlBulkCopy用于批处理/远程处理，因此您无法直接访问它实际执行INSERT的会话。因此，您需要一个完全独立的机制来捕获值。触发器就是这样的机制。您可以使用触发器将新ID以及标识字段插入另一个表中，在SqlBulkCopy完成后可以从中读取。

创建一个表来暂时存储新 ID。如下所示，请注意所有字段都应为 NOT NULL，因为任何可以包含 NULL 的字段都不能成为 PK 的一部分，因此在此处不适合您的需求。 
CREATE TABLE SchemaName.TempIDs  
(  
   ID INT NOT NULL,  
   IdentifyingField1 DataType NOT NULL,  
   ....  
   IdentifyingFieldN DataType NOT NULL,  
   CreatedDate DATETIME NOT NULL  
               CONSTRAINT [DF_TempIDs_CreatedDate] DEFAULT (GETDATE())  
);

在导入表上创建触发器，将相关数据插入TempIDs表中： 
CREATE TRIGGER SchemaName.ImportTable_CaptureIDs
ON SchemaName.ImportTable
AFTER INSERT
AS
   SET NOCOUNT ON;

   INSERT INTO SchemaName.TempIDs
               (ID, IdentifyingField1, ..., IdentifyingFieldN)
      SELECT ID, IdentifyingField1, ..., IdentifyingFieldN
      FROM INSERTED;

更新您的应用程序代码以在流程开始时 DELETE FROM SchemaName.TempIDs;。当然，您需要考虑到流程可能会失败，并根据应用程序代码编写的方式来看，如果您可以再次将子数据加载到内存中以插入到相关表中，则不需要调用删除操作。在这种情况下，您需要先检查是否有记录在 SchemaName.TempIDs 中，如果是，则处理它们，然后再删除它们。
在 SqlBulkCopy 步骤之后更新应用程序代码，以 选择 ID、IdentifyingField1、...、IdentifyingFieldN。您将使用 IdentifyingFieldX 字段将新的 ID 字段值与内存中的集合匹配，将其插入到适当的记录中。
这更像是一种过度的解决方案。没有理由创建触发器。通过以不被预期的方式使用 SqlBulkCopy 获得的任何性能增益可能会导致净损失。 - Cam Bruce
@CamBruce：感谢您的反馈，但我不同意。SqlBulkCopy旨在高效/批量插入数据，而不仅仅是从服务器到服务器，正如您在答案中所述。如果您查看问题中的链接，他正在传递一个DataRow数组，因此来自应用程序的多个行。这是SqlBulkCopy的几个预期用途之一。一个简单的触发器插入到没有争用的表中将几乎没有影响。由于插入集比您建议的单行插入更快，因此可能您的建议实际上比我的更慢。 - Solomon Rutzky
@CamBruce：现在OP已经确认他们确实一次插入了数千条记录，因此我在这里提出的解决方案（即触发器）远非“过度解决方法”。从应用程序中插入数千条记录是SqlBulkCopy的可接受和预期使用方式，而触发器对性能的影响可以忽略不计。相比之下，即使在事务中进行单行插入，也会比使用SCOPE_IDENTITY()慢得多/更糟糕。 - Solomon Rutzky
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接