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.