INSERT-OUTPUT including column from other table

sql sql-server tsql sql-insert
p.s.w.g picture p.s.w.g · Mar 7, 2013 · Viewed 9.1k times · Source

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?

Answer

a1ex07 picture a1ex07 · Mar 7, 2013

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

Related questions

Avoid duplicates in INSERT INTO SELECT query in SQL Server

I have the following two tables: Table1 ---------- ID Name 1 A 2 B 3 C Table2 ---------- ID Name 1 Z I need to insert data from Table1 to Table2. I can use the following syntax: INSERT INTO Table2(Id, Name) SELECT Id, …

Read More
Inserting large number of records without locking the table

I am trying to insert 1,500,000 records into a table. Am facing table lock issues during the insertion. So I came up with the below batch insert. DECLARE @BatchSize INT = 50000 WHILE 1 = 1 BEGIN INSERT INTO [dbo].[Destination] (proj_details_sid, period_sid, …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More