Eliminating cursor operation in batch insert (identity PK)

Question

Eliminating cursor operation in batch insert (identity PK)

André Cardoso

SSCommitted

Points: 1745
More actions
January 19, 2006 at 6:01 am

#92854
How do you approach eliminating a cursor for "batch" inserting correlated items, when the parent item uses an identity as PK (for example, inserting a batch of orders and the corresponding orderItems)?
I have two approaches, but neither is very ellegant:
- Reserve some IDs on the table and enable "identity insert" to insert the calculated IDs and the child items (locking the table, while the identity insert is turned on);
- Add another column to the parent table to insert an ID with which I can relate the order and order items
Another approach is with the help of triggers, but I'm not very fond of using triggers

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Joe Nakanishi-3 SSC Eights! Points: 808 More actions · Answer 1

You can almost always eliminate a cursor by using a WHILE loop.

In a case like this I've used a temp table for the parent item and create a identity column, get the next identity value from the parent table and start your identity there. Then it's just a couple of insert statements to populate your parent and child data.

André Cardoso SSCommitted Points: 1745 More actions · Answer 2

That's the kind of processing that I want to avoid (row by row). I'm looking for set-based solutions/approaches.

André Cardoso SSCommitted Points: 1745 More actions · Answer 3

I'm sorry I didn't read your answer carefully. That approach is similar to the first one that I described.

The reason I think it isn't very elegant, it's because you must lock the parent table (not allowing any inserts that use up the identity values) or use dbcc to reset the identity to another value.

Then, you must enable identity insert on the table, and meanwhile, you cannot allow inserts (from an application) on the table, because of the identity insert.

Phil.Nicholas SSCrazy Points: 2097 More actions · Answer 4

Something like this would probably work

create a table to store a record (unique index so only one allowed) to prevent two people inserting records via sp.

i.e if exists(select * from tblLocks where tablename = 'mytable')

raiserror its locked

else

insert Lock record

if @@error !=0 return

shift identity key x records on

identity insert on

insert records

identity insert off

delete from tblLock where name = 'mytable'

return new ids

Phil Nicholas

André Cardoso SSCommitted Points: 1745 More actions · Answer 5

Is it possible to reliably shift the identity key? I normally use DBCC CHECKIDENT (table, RESEED, value), but it's a bit of a hack, and difficult to catch any error.

Because of that, I tried the second approach (insert a new key column in the parent table, an AK, so that I can get the identity key and relate the parent and child table records, based on the AK). The problem with this approach is the need to add a new column to the schema (not always a problem).

Phil.Nicholas SSCrazy Points: 2097 More actions · Answer 6

Thinking about it if you are inserting in a batch wouldn't sql server allocate a batch of keys, so you only need the recordcount and the scope identity? Have you played around with multiple concurrant batch inserts?

Phil Nicholas

André Cardoso SSCommitted Points: 1745 More actions · Answer 7

I haven't thought of that. As scope identity returns just a value, I put it aside, but with the record count, maybe I can work around it.

Thanks for all the opinions,

André Cardoso