How to get id of records inserted into a table by using SQLBULKCOPY?

Question

How you can get the id of the inserted data using sqlbulkcopy? The id is needed for the formation of the child table. Download example I saw on msdn (http://msdn.microsoft.com/en-us/library/ac44f8yy(v=vs.110).aspx) and it works. But how to get id is not written. Program code in c#

Answer 1

It's not possible. You will have to use traditional INSERT statements using scope_identity() or using the OUTPUT clause to get the values of IDs - assuming the ID's are coming from IDENTITY column types.

SqlBulkCopy isn't meant for transactional inserts. It's meant to copy large amounts of data between servers.

Answer 2

If you are inserting multiple rows but do not need any of the special features of SqlBulkCopy, you should consider using Table-Valued Parameters (TVPs). You can create a Stored Procedure that accepts a TVP and returns a result set. The body of the Stored Procedure would be something along the lines of:

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;

Your app code would call the proc via SqlCommand.ExecuteReader() and you would get the new IDs and the identifying fields back as a SqlDataReader.

TVPs were introduced in SQL Server 2008. If you are using SQL Server 2005 you can still send in a batch of rows but only as XML. However, even as XML you can still do the multiple-row insert and the OUTPUT clause to capture the new IDs as shown above.

I wrote an article several years ago showing a few different options for calling Stored Procs that have Table-Valued Parameters from C# code. You can find it here, Streaming Data Into SQL Server 2008 From an Application, though free registration is required to read articles on that site.

Another example can be found in this answer, How to pass a table-value parameter, which shows the creation of the Table Type, the Stored Procedure (no OUTPUT clause), and the C# code.

Answer 3

One way to have the IDs is by forcing them into your identity using:

SET IDENTITY_INSERT (Transact-SQL)

use database
go

set identity_insert myDatabase.mySchema.MyTable on
go

...

set identity_insert myDatabase.mySchema.MyTable off
go

Those hardly apply to client code processes, though.

In addition to it, I have never attempted such procedures directly from within client code (C#, VB, whatever).

As stated by @CamBruce, those are meant for batch processes or else. Perhaps would you be best with a INSERT...SELECT... statement as stated in the reference in your question:

SqlBulkCopy.WriteToServer Method (DataRow[])

IMPORTANT:

If the source and destination tables are in the same SQL Server instance, it is easier and faster to use a Transact-SQL INSERT … SELECT statement to copy the data.

Answer 4

Assuming the following two statements to be true of your particular situation:

1. There is at least one, if not more, fields of the input data that uniquely identify the data (i.e. they could be used as a PK if you weren't going to use the IDENTITY field).
2. The process of this app code calling SqlBulkCopy is single-threaded (i.e. only one import process will / can run at a time).

then there an option. As others have stated, SqlBulkCopy is for batched / remote processing so you don't have direct access to the session in which it actually does the INSERT. Hence, you need a fully independent mechanism to capture values. Triggers are such a mechanism. You can have a trigger insert the new IDs along with the identifying field(s) into another table that you can read from after SqlBulkCopy finishes.

1. Create a table to temporarily hold the new IDs. Something along the lines of the following. Please note that all fields should be NOT NULL as any field that can contain NULLs could never be a part of a PK and hence are not suitable for your needs here.

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

2. Create a Trigger on your import table to insert the relevant data into the TempIDs table:

   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;
   

3. Update your app code to DELETE FROM SchemaName.TempIDs; at the beginning of the process. Of course, you need to consider that the process might fail and depending on how your app code is written, if you can get the child data into memory again to insert into the related tables, then you won't want to call the delete until that is taken care of. In that case, you will want to first check to see if any records are in SchemaName.TempIDs and if so, process them and then delete them.

4. Update your app code to SELECT ID, IdentifyingField1, ..., IdentifyingFieldN after the SqlBulkCopy step. You would use the IdentifyingFieldX fields to match up to your collection in memory to graft the new ID field values into the appropriate records.