Using OUTPUT INSERTED.id to UPDATE (i.e. not insert new record) existing rows

Question

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.

Answer 1

You could do one of two things.

Insert all inserted values into a holding table variable and then update the original table variable with the inserted values such as this code below -

DECLARE @MULTIPLE_RESULTS_TABLEVAR TABLE
(
    x CHAR(1)
  , y CHAR(1)
  , OrdersTableID INT
);
DECLARE @HOLDING_TABLE TABLE
(
    x CHAR(1)
  , y CHAR(1)
  , OrdersTableID INT
);
INSERT INTO @MULTIPLE_RESULTS_TABLEVAR
(
    x
  , y
  , OrdersTableID
)
VALUES
('a', 'b', NULL)
, ('c', 'd', NULL)
, ('e', 'f', NULL);
CREATE TABLE #Orders_Table
(
    OrdersTableID INT IDENTITY(1, 1)
  , x CHAR(1)
  , y CHAR(1)
);
INSERT INTO #Orders_Table
OUTPUT Inserted.x
     , Inserted.y
     , Inserted.OrdersTableID
INTO @HOLDING_TABLE
SELECT mrt.x
     , mrt.y
FROM @MULTIPLE_RESULTS_TABLEVAR AS mrt;
UPDATE mrt
SET mrt.OrdersTableID = ht.OrdersTableID
FROM @MULTIPLE_RESULTS_TABLEVAR AS mrt
    INNER JOIN @HOLDING_TABLE AS ht
        ON ht.x = mrt.x
           AND ht.y = mrt.y;
SELECT mrt.x
     , mrt.y
     , mrt.OrdersTableID
FROM @MULTIPLE_RESULTS_TABLEVAR AS mrt;
SELECT ot.OrdersTableID
     , ot.x
     , ot.y
FROM #Orders_Table AS ot;
DROP TABLE #Orders_Table;

That gives me these results -

-- @MULTIPLE_RESULTS_TABLEVAR
x    y    OrdersTableID
---- ---- -------------
a    b    1
c    d    2
e    f    3
-- #Orders_Table
OrdersTableID x    y

1             a    b
2             c    d
3             e    f

Or insert the values into @MULTIPLE_RESULTS_TABLEVAR, self-join on x and y, and delete the records where OrdersTableID is null like this -

DECLARE @MULTIPLE_RESULTS_TABLEVAR TABLE
(
    x CHAR(1)
  , y CHAR(1)
  , OrdersTableID INT
);
INSERT INTO @MULTIPLE_RESULTS_TABLEVAR
(
    x
  , y
  , OrdersTableID
)
VALUES
('a', 'b', NULL)
, ('c', 'd', NULL)
, ('e', 'f', NULL);
CREATE TABLE #Orders_Table
(
    OrdersTableID INT IDENTITY(1, 1)
  , x CHAR(1)
  , y CHAR(1)
);
INSERT INTO #Orders_Table
OUTPUT Inserted.x
     , Inserted.y
     , Inserted.OrdersTableID
INTO @MULTIPLE_RESULTS_TABLEVAR
SELECT mrt.x
     , mrt.y
FROM @MULTIPLE_RESULTS_TABLEVAR AS mrt;
DELETE mrt
FROM @MULTIPLE_RESULTS_TABLEVAR AS mrt
    INNER JOIN @MULTIPLE_RESULTS_TABLEVAR AS mrt2
        ON mrt2.x = mrt.x
           AND mrt2.y = mrt.y
WHERE mrt.OrdersTableID IS NULL;
SELECT mrt.x
     , mrt.y
     , mrt.OrdersTableID
FROM @MULTIPLE_RESULTS_TABLEVAR AS mrt;
SELECT ot.OrdersTableID
     , ot.x
     , ot.y
FROM #Orders_Table AS ot;
DROP TABLE #Orders_Table;

Both generate the same results, but this second solution requires that there is no pk set on @MULTIPLE_RESULTS_TABLEVAR for values x and y.