Identity values with multi-row INSERT
Posted on 2004-09-20
I have a feeling the answer to this question is going to be a resounding "no," but I 'm wondering if there's a way to accomplish this without using a cursor: I have a table that has no valid natural key, so I use an identity column as a surrogate key. At times, certain rows in this table need to be duplicated. In other words, a group of rows will be selected based on given criteria, and all columns from those rows (except, obviously, the identity) will be inserted into new rows in the same table (with, obviously, new identities). I have to be able to link each newly created row back to the row it was created from in order to create an audit trail of which rows were duplicated into which other rows. I may not be explaining this well; let me know if it seems unclear.
So far, the only way I have found to accomplish this is to use a cursor to loop through the records to be duplicated, inserting each new record, getting its identity using SCOPE_IDENTITY(), then creating the audit trail record at that point. This works OK most of the time, but it seems inefficient to me. Does anyone have a better suggestion?
Thanks in advance!