INSERT-OUTPUT including column from other table
10,005
I'm not sure if that is the best option, but it seems you can do the trick using MERGE
:
MERGE [Contacts] trgt
USING
(
SELECT [First_Name], [Last_Name], g.[GUID] as [GUID]
FROM [SourceTable] s
JOIN @guids g ON s.[GUID] = g.[GUID]
)src ON (1=0)
WHEN NOT MATCHED THEN INSERT ( [FirstName], [LastName], [ModifiedDate] )
VALUES (src.[First_Name],src.[Last_Name], GETDATE())
OUTPUT [inserted].[ContactID], src.[GUID]
INTO @contacts
![p.s.w.g](https://i.stack.imgur.com/4B4y1.png?s=256&g=1)
Author by
p.s.w.g
Updated on June 13, 2022Comments
-
p.s.w.g about 2 years
I have a stored procedure that needs to insert into three different tables, but I need to get the ID generated from the one input and use that to insert into the next table. I'm familiar with the
INSERT-OUTPUT
construct, but I'm not sure how to go about using it in this particular case.DECLARE @guids TABLE ( [GUID] UNIQUEIDENTIFIER ); DECLARE @contacts TABLE ( [ContactID] INT, [GUID] UNIQUEIDENTIFIER ); DECLARE @mappings TABLE ( [TargetID] INT, [GUID] UNIQUEIDENTIFIER ); INSERT @guids ( [GUID] ) ... INSERT [Contacts] ( [FirstName], [LastName], [ModifiedDate] ) OUTPUT [inserted].[ContactID], g.[GUID] INTO @contacts SELECT [First_Name], [Last_Name], GETDATE() FROM [SourceTable] s JOIN @guids g ON s.[GUID] = g.[GUID] INSERT [TargetTable] ( [ContactID], [License], [CreatedDate], [ModifiedDate] ) OUTPUT [inserted].[TargetID], c.[GUID] INTO @mappings SELECT c.[ContactID], [License], [CreatedDate], [CreatedDate] FROM [SourceTable] s JOIN @contacts c ON s.[GUID] = c.[GUID] INSERT [Mappings] ( [TargetID], [SourceGUID] ) SELECT [TargetID], [GUID] FROM @mappings
But I get the following errors:
The multi-part identifier "g.GUID" could not be bound.
The multi-part identifier "c.GUID" could not be bound.
I'll get similar errors if I use
s.GUID
instead. Is it possible to do a kind of join in theOUTPUT
clause?