Using OUTPUT to capture identity columns after insert

Question

Using OUTPUT to capture identity columns after insert

cormacdebarra

SSC Enthusiast

Points: 108
More actions
January 4, 2010 at 7:28 pm

#217941

Hi all,
I'm trying to copy rows in a table. So given the id of a row, it finds that row and inserts all the data from that row under a new identity. I need to capture the oldid and the new id during the process.
I have this
insert into tblschedule (projectID, comments, datecreated, OperatorIDCreated )
output inserted.scheduleID,
s.ScheduleID
into #tmp1
select 26 as projectID, scheduleID, getdate() as date, 8747 as operatorID
from tblschedule as s
where s.scheduleid in (482,483)
select * from #tmp1
but i get the error
The multi-part identifier "s.ScheduleID" could not be bound.
Does anyone know of a way i can get around this to capture the original id as well as the new id.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mike01 SSChampion Points: 11395 More actions · Answer 1

There's a couple of problems with your query. First you're not inserting anything into the ScheduleID field so it will always be null. You are inserting the scheduleID into the Comments field. Second, I'm not sure which value you want to get. Is it the before and after of the ProjectID? If so, then you can't do it this way since you're not even selecting it. Can you give an example of what you have and what you expect in the Temp table? If you want the old ProjectId, then can you insert it into the Comments field?

create table #tmp1 (scheduleID int,ProjectID int, OldProjectID int)

insert into tblschedule (projectID, scheduleID, comments, datecreated, OperatorIDCreated )

output inserted.scheduleID, inserted.ProjectID, cast(inserted.Comments as int)

into #tmp1

select 26 as projectID, scheduleID, projectID, getdate() as date, 8747 as operatorID

from tblschedule as s

where s.scheduleid in (482,483)

select * from #tmp1

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

cormacdebarra SSC Enthusiast Points: 108 More actions · Answer 2

Thanks Mike,

Sorry if its not clear.

What im basically trying to do is copy a row in tblSchedule and give it a new ScheduleID, which is the primary key on the table.

So the tmp table should have after the query :

The scheduleID that was copied and the new scheduleID

[

insert into tblschedule (projectID, OperatorIDCreated )

output inserted.scheduleID, s.ScheduleID

into #tmp1

select projectID, operatorIDCreated

from tblschedule as s

where s.scheduleid in (482,483)

select * from #tmp1

Something like that. Does that make sense?

Thanks

Mike01 SSChampion Points: 11395 More actions · Answer 3

Your first query is different from your second one. I used the first one.

On an insert, I don't believe you can access the table you are selecting from, but since you're putting the old ScheduleID in the comments field, you can use that field in your output statement.

insert into tblschedule (projectID, comments, datecreated, OperatorIDCreated )

output inserted.scheduleID, inserted.comments

into #tmp1

select 26 as projectID, scheduleID, getdate() as date, 8747 as operatorID

from tblschedule

where scheduleid in (482,483)

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/