Inserting into tables with indentity turned on

Question

Inserting into tables with indentity turned on

jpperedo

SSC Rookie

Points: 33
More actions
August 9, 2004 at 3:27 pm

#163043

Hi! Just been curios here:
Assume that you have a parent table A
and its child table B.
The two table are related by A's identity column.
Now assume that you have several records that need to be inserted
into A and that you also need to insert the corresponding child records into B.
Is there a better way to do this than having to:
Open a cursor to all the records that need to be inserted in A
For each record in the cursor
Insert into table A
Get the new id using @@scope_identity
Insert into B using A's new id
End
Close cursor
It would seem to me that given that:
Cursor are to be avoided whenever possible and that
Identity columns are widely used as primary keys and foreign keys,
there must be a better way to do this.
Thanks in advance!

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jonathan Tomkins Ten Centuries Points: 1268 More actions · Answer 1

In that scenario, I'd suggest that an identity field may be making your life harder than it needs to be.

You could have a unique sys ref (maybe an identity) and a unique user ref, so e.g. you want to store products and have ProductId and ProductName - in those cases you'd be able to use the ProductName for inserts, and look up the ProductId for inserts on a related table.

I would say that I don't think cursors are an "avoid whenever possible" - it depends on what you're doing. It may not perform as well as another query, but if you're only running it once a minute and it takes 400ns rather than 1ns, it may not be any issue.

That said, when I have found myself wanting to do as you describe, I've normally found that the id field didn't need to be an identity, just a unique int where I could write in whatever number I liked - could just be lucky though!

Iain Parker SSC-Addicted Points: 409 More actions · Answer 2

Take a look at what you can do with triggers. if you add an insert trigger to the parent table you can (probably) write all the child records in one go using the "inserted" table and keep the identity field.

Have fun!

iain

Regards,Iain

EdH334 SSCrazy Points: 2857 More actions · Answer 3

Another possibility is to insert all records from the source into A, then insert into B joining the source with A to select the identity field. This method assumes that there is a way of uniquely identifying each of the source records when joining to A.

Alternatively, if you have exclusive access to the data when inserting (or can lock the table for long enough), and your source is a temp table, try getting the max identity in A and create an identity field on your temp table which numbers from the next number. You can then SET IDENTITY_INSERT dbo.A ON, and directly insert the source data into A, and then use the same field to insert into B. Remember to set the IDENTITY_INSERT off again afterwards.