April 2, 2019 at 1:53 pm
I completely agree that the implementation of the OUTPUT clause for INSERT is... shall we say, disappointing.
And you're correct that there may be obstacles to modifying prod tables.
Another workaround I've used is to OUTPUT...INTO a temp table the new identity and the candidate key columns, then updating that table with the old id.
April 2, 2019 at 2:14 pm
INSERT not being able to reference tables in the FROM clause is documented (OUTPUT Clause (Transact-SQL) - Arguments):
from_table_name
Is a column prefix that specifies a table included in the FROM clause of a DELETE, UPDATE, or MERGE statement that is used to specify the rows to update or delete.
If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.
Note that the documentation makes no mention of INSERT. If you do need to reference tables in the FROM clause in an OUTPUT clause, you would need to use a MERGE. Why, I don't know, but I suspect it's because there normally isn't a requirement. What you're after here isn't the "old" ID, it's a value from your existing data so that you can build a relationship between the existing data (in another table) and new rows. If you're building a relationship, then that foreign key would be being inserted into table so there is no need to be able to output values from the FROM (if the data is important and needs to be retain it would be being inserted).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
April 3, 2019 at 9:26 am
---- Quote :-------------------------------------------------------------------------------------------------------------------------
I completely agree that the implementation of the OUTPUT clause for INSERT is... shall we say, disappointing.
And you're correct that there may be obstacles to modifying prod tables.
Another workaround I've used is to OUTPUT...INTO a temp table the new identity and the candidate key columns, then updating that table with the old id.
---- End of Quote :------------------------------------------------------------------------------------------------------------------
This only works if you have next to the identity a set of candidate key columns. If the database (or table) is implemented with a least the 2nd normal form, there is not a set of candidate keys next to the identity. Although most tables 'probably' have a 'second' candidate key, not all tables have.
Ben
Viewing 3 posts - 16 through 17 (of 17 total)
You must be logged in to reply to this topic. Login to reply