Sample Header Ad - 728x90

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