SQL Server inserting to two tables linked via an auto-increment ID column

I recently ran into an interesting SQL problem...thought I would share what I came up with and see if anyone has an other ideas.

So, the basic layout of the problem is this:

I have a record set (in a temp table) with some columns that need to be inserted into one table, and some columns that will be inserted into another. Only some of the records are going to be inserted. The first table that I'm inserting to has an auto-incremented ID column, and the value from this column will need to be copied to the corresponding rows in the second table. The values that will be inserted into the first table are not necessarily unique in and of themselves, so I can't just insert into the first table and then select out the matching IDs that were generated.

My solution was to generate an "insert index" value for each record to be inserted in the input record set, insert these records (in order), grab the last ID allocated, and then use this to update the input temp table so it can then be inserted into the second output table.

Luckily, if you use an order by when inserting into a table with an auto-increment ID column, SQL Server guarantees that the new IDs will be allocated in the order you specify (although the records may by physically inserted in a different order).

Anyway, without further ado, here is some example code...feel free to let me know what you think:


-- @TempA is the first output table
DECLARE @TempA TABLE ( id INT IDENTITY(320, 1), something VARCHAR(100) )

-- @TempB is the temp table that contains the data to be inserted
DECLARE @TempB TABLE (
      rowindex INT IDENTITY(1, 1),
      insertindex INT NULL,
      doinsert bit,
      id INT NULL,
      something VARCHAR(100),
      somethingelse VARCHAR(100 )
)

-- some variables...
DECLARE @id INT, @insertindex INT

-- populate the "insert" data set along with flags determining
-- whether the record will be inserted
INSERT INTO @TempB
( something, somethingelse, doinsert )
VALUES
( 'something1', 'else1', 0 ),
( 'something2', 'else2', 1 ),
( 'something1', 'else3', 1 ),
( 'something2', 'else4', 0 ),
( 'something1', 'else5', 1 ),
( 'something2', 'else6', 1 ),
( 'something1', 'else7', 1 )

-- initialize the counter variable
SET @insertindex = 0

-- generate ordering values for the records to be inserted
UPDATE @TempB
SET @insertindex = insertindex = @insertindex + 1
WHERE doinsert = 1

-- insert the records that are to be inserted
INSERT INTO @TempA
( something )
SELECT something
FROM @TempB
WHERE doinsert = 1
ORDER BY rowindex

-- grab the last identity that was auto-generated by the insert
SET @id = @@identity

-- update the temp table with the input record set with the
-- auto-generated ID so it can be inserted into the other table
UPDATE @TempB SET id = (@id - @insertindex) + insertindex
WHERE doinsert = 1

-- check to make sure the correct IDs were copied
SELECT * FROM @TempB