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
– Jason Whitish, Commented Mar 7, 2013 at 21:45

a1ex07 · Accepted Answer · 2013-03-07 23:15:45Z

41

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 7, 2013 at 23:15

answered Mar 7, 2013 at 21:31

a1ex07

37.4k12 gold badges93 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

p.s.w.g Over a year ago

Please forgive my ignorance. I've never used MERGE or even seen it used in the wild. Could you explain this code a bit?

GilM Over a year ago

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.

Mikael Eriksson Over a year ago

@GilM and a question about it here

Reversed Engineer Over a year ago

@MikaelEriksson Or a newer link to Dr. OUTPUT or: How I Learned to Stop Worrying and Love the MERGE if the other one doesn't work.

Collectives™ on Stack Overflow

INSERT-OUTPUT including column from other table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related