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
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