Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows
0
votes
1
answer
672
views
1.) @MULTIPLE_RESULTS_TABLEVAR has fields (x, y, OrdersTableID) and values:
[a,b,Null], [c,d,Null], [e,f,Null]
2.) Goal is to bulk insert @MULTIPLE_RESULTS_TABLEVAR data into an OrdersTable having fields (id, x, y) with each ORDERS_TABLE.id (aka identity) returned to ***update*** @MULTIPLE_RESULTS_TABLEVAR to make the values:
[a,b,1], [c,d,2], [e,f,3]
3.) But using OUTPUT INSERTED.id INTO @MULTIPLE_RESULTS_TABLEVAR ***adds*** new rows to @MULTIPLE_RESULTS_TABLEVAR yielding values:
[a,b,Null], [a,b,Null], [a,b,Null], [NULL,NULL,1], [NULL,NULL,2], [NULL,NULL,3]
4.) I can't find a documentation option or non-kludgy strategy to ***UPDATE*** the existing rows. Specifically I don't want to trust a
( LAST_INSERT_@@SCOPE_IDENTITY - count(MULTIPLE_RESULTS_TABLEVAR.id) )
while echoing to a new #temptable or a CURSOR/LOOP to INSERT then UPDATE with @@SCOPE_IDENTITY
seems to defeat the whole purpose of **OUTPUT INSERTED**.
Asked by M S
(21 rep)
Sep 12, 2023, 12:14 AM
Last activity: Apr 16, 2025, 10:03 AM
Last activity: Apr 16, 2025, 10:03 AM