How to include a reference column with the OUTPUT in SQL

Question

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?

Answer 1

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