INSERT-OUTPUT including column from other table

Question

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 the OUTPUT clause?

You're getting the error because you can't use anything other than the INSERTED or DELETED references on inserts (which means you can only use INSERTED, of course). With updates and deletes, you can reference the other tables used in your query. The merge option below should work so long as you're using SQL Server 2008 or higher. The only way I've found to do it otherwise is to find an unused column on the table I'm inserting into and put the value (in your case, the guid) into that column, then go back and pull out the column later (and update it back to null so it doesn't stay there). — Jason Whitish, Mar 07 '13 at 21:45

a1ex07 · Accepted Answer · 2013-03-07T23:15:45.540

39

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

edited Mar 07 '13 at 23:15

answered Mar 07 '13 at 21:31

a1ex07

36,826
12
90
103

Please forgive my ignorance. I've never used `MERGE` or even seen it used in the wild. Could you explain this code a bit? – p.s.w.g Mar 07 '13 at 22:01
4

[Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE](http://sqlblog.com/blogs/adam_machanic/archive/2009/08/24/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge.aspx) – Mikael Eriksson Mar 07 '13 at 22:04
5

That's an excellent article. But, the short answer to your question is that MERGE allows one to combine any combination of INSERT, UPDATE and DELETE in a single statement (this code forces an INSERT). MERGE is useful for you here because it allows using columns from different tables in the OUTPUT clause, while INSERT doesn't. – GilM Mar 07 '13 at 22:28
1

@GilM and a question about it [here](http://stackoverflow.com/questions/5365629/using-merge-output-to-get-mapping-between-source-id-and-target-id) – Mikael Eriksson Mar 07 '13 at 22:31
4

@MikaelEriksson Or a newer link to [Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE](http://dataeducation.com/dr-output-or-how-i-learned-to-stop-worrying-and-love-the-merge/) if the other one doesn't work. – Reversed Engineer Apr 23 '18 at 18:56

INSERT-OUTPUT including column from other table

1 Answers1

Linked

Related