include column from table in SELECT in an INSERT OUTPUT clause

Hey All –

I am having problems with the OUTPUT clause in an INSERT.

I have something like this:

INSERT INTO dbo.Person

( PersonID

,Name

)

OUTPUT p.AuditVersionGUID

,inserted.PersonID

,inserted.Name

INTO dbo.PersonAudit

(

AuditVersionGUID

,PersonID

,Name

)

SELECT p.PersonID, p.Name

FROM #Person p

AuditVersionGUID is not and shouldn’t be defined on the Person table but I do have it defined in my #Person table.

I get this error:

Msg 4104, Level 16, State 1, Procedure spExtractPerson, Line 275

The multi-part identifier "P.AuditVersionGUID" could not be bound.

I can accomplish this when I am using the OUTPUT clauses in DELETE and UPDATE statements but not the INSERT.

Is this possible or am I relegated to using a memory table and appending the GUID in a separate statement after the OUTPUT?

Thank you,

Cameron

Yeah the GUID has already been determined since a few other sprocs need to refer to the same one.

Thank you for confiming what i had thought. Seems kind of strange why it only doesnt work on INSERT.

Cameron

|||

It's not allowed. Only { DELETED | INSERTED | from_table_name } . { * | column_name } is allowed in OUTPUT clause. from_table_name is a table included in the FROM clause of a DELETE or UPDATE statement that is used to specify the rows to update or delete.

But the following equivalent statement should work for you.

-- swap PersonAudit and Person

INSERT INTO dbo.PersonAudit

( AuditVersionGUID

,PersonID

,Name

)

OUTPUT

inserted.PersonID

,inserted.Name

INTO dbo.Person

(

PersonID

,Name

)

SELECT p.AuditVersionGUID, p.PersonID, p.Name

FROM #Person p

go

|||Ah, that would work, but Person is in a FK relationship, which OUTPUT does not allow the table to be on any side of a FK relationship.|||

In that case, then, you could always load a variable with the GUID and return it in the OUTPUT clause:

OUTPUT @.MyGUID,

inserted.[PersonID],

inserted.[name]

Obviously I don't understand your complete scenario so this may or may not be of any help.

Chris

|||It is doing a bulk insert so i can't use a variable because each AuditVersionGUID is linked to a specific PersonID, which is why i was trying to get it from the tables in the SELECT|||

In the OUTPUT clause you cannot reference tables other than INSERTED or DELETED when performing an INSERT as you can when performing a DELETE or an UPDATE. Look up the 'OUTPUT Clause' topic in BOL for more info.

Incidentally, in your example code has the value of the GUID already been determined by the time that you use the OUTPUT clause?

If not then you could simply use the following to generate a GUID:

OUTPUT NEWID(),

inserted.[PersonID],

inserted.[name]

Chris