0

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.

2
  • Do the columns x, y in @MULTIPLE_RESULTS_TABLEVAR always contain unique combination of values?
    – J.D.
    Commented Sep 12, 2023 at 12:27
  • 1
    YES, the columns x, y in @MULTIPLE_RESULTS_TABLEVAR always contain a unique combination of values.
    – M S
    Commented Sep 12, 2023 at 14:45

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.