Post reply

A Tough One, At Least For Me

  • January 18, 2012 at 1:41 pm

    #1435486

    fahey.jonathan (1/18/2012)

    Sean Lange (1/18/2012)

    How can you get a value from an insert that isn't in the inserted table? If you insert it, it is in the inserted table. The inserted table IS the insert (including an identity values).

    In the original problem, the user had OldID and Name in OldTable. He wanted to insert the Name into NewTable that assigned a NewID but did not contain an OldID field. He wanted to have a cross-reference of OldID and NewID.

    INSERT INTO NewTable (Name) SELECT Name FROM OldTable

    Using an OUTPUT clause on that INSERT will give us NewID and Name only, not OldID because we can only reference INSERTED and DELETED, and OldID was not an inserted field.

    Using the MERGE statement, we have access to the "from_table_name" syntax, meaning we can get OldTable.OldID and INSERTED.NewID in the same OUTPUT statement, giving us the desired cross-reference.

    That's exactly what my situation was.

Viewing post 16 (of 15 total)

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