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?
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);