T-SQL Output Clause: How to access the old Identity ID

Question

I have a T-SQL statement that basically does an insert and OUTPUTs some of the inserted values to a table variable for later processing.

Is there a way for me to store the old Identity ID of the selected records into my table variable. If I use the code below, I get "The multi-part identifier "a.ID" could not be bound." error.

DECLARE @act_map_matrix table(new_act_id INT, old_ID int)
DECLARE @new_script_id int
SET @new_script_id = 1

INSERT INTO Act
(ScriptID, Number, SubNumber, SortOrder, Title, IsDeleted)
OUTPUT inserted.ID, a.ID INTO @act_map_matrix
    SELECT 
        @new_scriptID, a.Number, a.SubNumber, a.SortOrder, a.Title, a.IsDeleted
    FROM Act a WHERE a.ScriptID = 2

Thanks!

Follansbee

what "old" identity ID?? you're inserting new rows.... – Allene

yes, I'm inserting new rows but for further processing I need a way to map my new rows to the records that was used in creating these new rows. I have to have a way to map the new row and its corresponding record. – Follansbee

Yes, I know I can do that with an Update but unfortunately I have to do an Insert. – Follansbee

You're right. Old ID is not probably the proper way to call it. What I mean by Old ID is the Identity ID of the records as a result of the SELECT statement used. – Follansbee

"if you insert a new record into "Act", aren't "inserted.ID" and "a.ID" going to be the very same value??? " No. inserted.ID is a new Identity ID of the INSERTed row as against a.ID which is the identity of the SELECTed row. – Follansbee

Answer 1

You'll have to join back @act_map_matrix onto Act to get the "old" value.

It's simply not available in the INSERT statement

edit: one hopes that @new_scriptID and "scriptid=2" could be the join column

Lovieloving

That is what I was afraid of. But if I join back to Act via @act_map_matrix, how can I get the "old ID" value if my @act_map_matrix contains the NEW IDs. – Follansbee

I did not understand what you meant by joining back to the @act_map_matrix but eventually figured it out. DUH!!! Thanks for the help gbn!!! – Follansbee

Can you explain your solution. I need something that works on SQL2005 – Whitcomb

Answer 2

I was having your same problem and found a solution at http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx

Basically it hacks the MERGE command to use that for insert so you can access a source field in the OUTPUT clause that wasn't inserted.

MERGE INTO people AS tgt
USING #data AS src ON
    1=0 --Never match
WHEN NOT MATCHED THEN
    INSERT
    (
        name,
        current_salary
    )
    VALUES
    (
        src.name,
        src.salary
    )
OUTPUT
    src.input_surrogate,
    inserted.person_id
INTO #surrogate_map;

Heber

This is great Brandon, Thanks! But unfortunately I we are using SQLServer 2005 and I believe it does not support Merge. However, this code is nice to know if and when we move to 2008. Thanks again. – Follansbee