2

With SQL Server 2K8 from C# I'm trying to do a batch insert/updates of records to a parent/child tables to optimize.

The inserts/updates will generate a key automatically which I'd like to extract via an OUTPUT, etc. and then reassign back in the domain model. For batch inserts I need to keep track of which newly generated ID belongs to which domain object in the batch list.

This example comes close to what I need, but was wondering if there's a way to not have an extra column added to the table (SequenceNumber) and still achieve the same results: http://illdata.com/blog/2010/01/13/sql-server-batch-inserts-of-parentchild-data-with-ibatis/

ie. could we rely on the order of the inserts generated from the OUTPUT into the temp table, or pass a ref GUID set on the data model and passed temporarily to the SQL just for reference purposes?

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
soundslike
  • 393
  • 3
  • 12
  • 1
    Since you are in SQL Server 2008 you can use `merge...output` to get a mapping between the generated key and the key used in the staging table. http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id – Mikael Eriksson Oct 15 '11 at 15:54
  • Perfect that was exactly what I needed Mikael! If you want you can post that as an answer so I can mark it as such. – soundslike Oct 15 '11 at 16:17

2 Answers2

1

In SQL Server 2008 it is possible to use merge and output to get a mapping between the generated key and the key used in the staging table.

Have a look at this question. Using merge..output to get mapping between source.id and target.id

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
0

Unless I've misunderstood...

A surrogate key (IDENTITY or NEWID etc) isn't your actual object identifier. It's an implementation detail and has no intrinsic meaning.

You must have another identifier (name, ISBN, serial number, transaction code/date, etc) that is the real (natural) key.

Your OUTPUT clause can return the surrogate key and the natural key. You then use this to map back

gbn
  • 422,506
  • 82
  • 585
  • 676
  • I have a class say People { int Id, string Name } and the table has ID as an IDENTITY column. But I have a batch list of People classes I want to insert. Initially I only populate the Name field, do the batch insert of all the People classes in my batch list, and then set the Id property on my C# class. I need to get the Id back from SQL Server set on the appropriate C# People class in my batch list. With the OUTPUT to a temp table I have a list of Ids created by SQL Server, but have no way of really knowing which C# People class I should assign it back to. – soundslike Oct 15 '11 at 15:34
  • Using the OUTPUT clause, you can get INSERTED.ID, INSERTED.Name. Then you match name to assign ID. You are not restricted to one column in an OUTPUT clause – gbn Oct 15 '11 at 15:40