2

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

user1531248
  • 521
  • 1
  • 5
  • 17
  • Please post your code of insert – Rohit Vyas Feb 18 '13 at 06:40
  • What's the problem? You want to preserve identity for bulk insert or you don't want it? Does your table has identity primary key? – Dennis Feb 18 '13 at 06:41
  • Possibly useful: [OUTPUT clause](http://msdn.microsoft.com/en-us/library/ms177564.aspx) (I use bulk-copy + MERGE here.) –  Feb 18 '13 at 06:44
  • Yes my parent table has identity primary key. The problem is parent/child tables bulk insert using sqlbulkcopy (Once i know the parent tables identity values then only i can play with child table) – user1531248 Feb 18 '13 at 06:46

0 Answers0