How to get auto-increment values after inserting records using SqlBulkCopy via c#

c# sql sqlbulkcopy stored-procedures

Question

I am using SqlBulkCopy class to insert bulk records but i want that newly generated auto-increment values(primary key plays Foreign key in child table) because with the help of that values i am going to play with child tables(Foreign key) making insert.

Please follow the below example to get the clear picture,

Source table: Create table StagingTable (personName varchar(50)) insert into StaginTable values('Test1') .. .. .. insert into StaginTable values('Test10,000')

Target table: Create table TargetTable (personId int identity(1,1), personName varchar(50)) insert into TargetTable values('Already1') .. .. insert into TargetTable values('Already10')

Now I am having 10 records in TargetTable with 10 records with personId (1 to 10), so my question is to insert 10,000 records from StagingTable to TargetTable & make it as 10,010 records meantime I need only 10,000 unique identity values by excluding 10 identity values with the help of merge concept in sql server 2012.

Create procedure Merge_GetNewIdentityValues As MERGE TargetTable AS T USING StagingTable AS S ON 1 != 1 when not matched then insert(personName) values(S.StagingTable) output inserted.personId; -- Get the identity field values [newly inserted one]

How can this be handled in concurrency? Whether "inserted" keyword plays in separate context or single context used for different users to call the same stored procedure

Thanks, S.Venkatesh

Accepted Answer

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.


Popular Answer

Fill the ColumnMapping of the BulkCopy object and don't map the identity column. The identity column will be generated by the target database.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why