Post reply

Inserting into tables with indentity turned on

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

  • August 10, 2004 at 3:45 am

    #518281

    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!

     

     

     

     

  • August 11, 2004 at 4:17 am

    #518441

    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

  • August 11, 2004 at 5:09 am

    #518447

    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.

     

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

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