What columns can be used in OUTPUT INTO clause?

Question

I'm trying to build a mapping table to associate the IDs of new rows in a table with those that they're copied from. The OUTPUT INTO clause seems perfect for that, but it doesn't seem to behave according to the documentation.

My code:

DECLARE @Missing TABLE (SrcContentID INT PRIMARY KEY )
INSERT INTO @Missing 
    ( SrcContentID ) 
SELECT cshadow.ContentID
    FROM Private.Content AS cshadow
    LEFT JOIN Private.Content AS cglobal ON cshadow.Tag = cglobal.Tag
    WHERE cglobal.ContentID IS NULL 

PRINT 'Adding new content headers'
DECLARE @Inserted TABLE (SrcContentID INT PRIMARY KEY, TgtContentID INT )
INSERT INTO Private.Content 
    ( Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, OrgUnitID ) 
    OUTPUT cglobal.ContentID, INSERTED.ContentID INTO @Inserted (SrcContentID, TgtContentID)
SELECT Tag, Description, ContentDate, DateActivate, DateDeactivate, SortOrder, CreatedOn, IsDeleted, ContentClassCode, ContentGroupID, NULL 
    FROM Private.Content AS cglobal
    INNER JOIN @Missing AS m ON cglobal.ContentID = m.SrcContentID

Results in the error message:

Msg 207, Level 16, State 1, Line 34
Invalid column name 'SrcContentID'.

(line 34 being the one with the OUTPUT INTO)

Experimentation suggests that only rows that are actually present in the target of the INSERT can be selected in the OUTPUT INTO. But this contradicts the docs in the books online. The article on OUTPUT Clause has example E that describes a similar usage:

The OUTPUT INTO clause returns values from the table being updated (WorkOrder) and also from the Product table. The Product table is used in the FROM clause to specify the rows to update.

Has anyone worked with this feature?

(In the meantime I've rewritten my code to do the job using a cursor loop, but that's ugly and I'm still curious)

Answer 1

I've verified that the problem is that you can only use INSERTED columns. The documentation seems to indicate that you can use from_table_name, but I can't seem to get it to work (The multi-part identifier "m.ContentID" could not be bound.):

TRUNCATE TABLE main

SELECT *
FROM incoming

SELECT *
FROM main

DECLARE @Missing TABLE (ContentID INT PRIMARY KEY)
INSERT INTO @Missing(ContentID) 
SELECT incoming.ContentID
FROM incoming
LEFT JOIN main
    ON main.ContentID = incoming.ContentID
WHERE main.ContentID IS NULL

SELECT *
FROM @Missing

DECLARE @Inserted TABLE (ContentID INT PRIMARY KEY, [Content] varchar(50))
INSERT INTO main(ContentID, [Content]) 
OUTPUT INSERTED.ContentID /* incoming doesn't work, m doesn't work */, INSERTED.[Content] INTO @Inserted (ContentID, [Content])
SELECT incoming.ContentID, incoming.[Content] 
FROM incoming
INNER JOIN @Missing AS m
    ON m.ContentID = incoming.ContentID

SELECT *
FROM @Inserted

SELECT *
FROM incoming

SELECT *
FROM main

Apparently the from_table_name prefix is only allowed on DELETE or UPDATE (or MERGE in 2008) - I'm not sure why:

• from_table_name

Is a column prefix that specifies a table included in the FROM clause of a DELETE or UPDATE statement that is used to specify the rows to update or delete.

If the table being modified is also specified in the FROM clause, any reference to columns in that table must be qualified with the INSERTED or DELETED prefix.

Answer 2

I'm running into EXACTLY the same problem as you are, I feel your pain... As far as I've been able to find out there's no way to use the from_table_name prefix with an INSERT statement. I'm sure there's a viable technical reason for this, and I'd love to know exactly what it is.

Ok, found it, here's a forum post on why it doesn't work: MSDN forums

Answer 3

I think I found a solution to this problem, it sadly involves a temporary table, but at least it'll prevent the creation of a dreaded cursor :) What you need to do is add an extra column to the table you're duplicating records from and give it a 'uniqueidentifer' type.

then declare a temporary table:

DECLARE @tmptable TABLE (uniqueid uniqueidentifier, original_id int, new_id int)

insert the the data into your temp table like this:

insert into @tmptable
(uniqueid,original_id,new_id)
select NewId(),id,0 from OriginalTable

the go ahead and do the real insert into the original table:

insert into OriginalTable
(uniqueid)
select uniqueid from @tmptable

Now to add the newly created identity values to your temp table:

update @tmptable
set new_id = o.id
from OriginalTable o inner join @tmptable tmp on tmp.uniqueid = o.uniqueid

Now you have a lookup table that holds the new id and original id in one record, for your using pleasure :)

I hope this helps somebody...