sqlsql-servert-sqlsql-insertoutput-clause

How to include a reference column with the OUTPUT in SQL


I have a reference TableA with a single column called [SomeID]:

SomeID
ABC
DEF
GHI
KLM

I have TableB can be:

CREATE TABLE TableB([ID] BIGINT, [Name] NVARCHAR(50))

[ID] is the primary key and is auto-increment.

I want to create a new record in TableB for each record of TableA.

So we do this:

DECLARE @OuputTable TABLE([ID] BIGINT, [SomeID] NVARCHAR(50))

INSERT INTO TableB([Name])
OUTPUT INSERTED.[ID], 'Need Associated SomeID From TableA Here' INTO @OutputTable
SELECT 'ZZZZZZ' -- Edited this line to remove some possible confusion.
FROM TableA

SELECT *
FROM
@OuputTable

How would I be able to place the associated [SomeID] value for each of the created record in @OuputTable without using a loop?


Solution

  • You can try to use MERGE INTO which might allow you get source data value in OUTPUT

    MERGE INTO TableB AS dest
    USING TableA AS sou ON 1=0   
    WHEN NOT MATCHED       
        THEN INSERT ([Name])
             VALUES (sou.[SomeID])
    OUTPUT INSERTED.[ID], sou.SomeID
    INTO @OutputTable (ID, SomeID);
    

    sqlfiddle