Getting id values from both source and target rows when duplicating records

Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?

I tried the following, but it doesn't seem to work:

INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @.number

You have to use the following logic, On OUTPUT clause you can’t use other than INSERT & DELETED table values. ID should be available on the columns list (if it is not a identity column).

Code Snippet

declare @.output table (id int, value1 int, value2 int,value3 int)

insert tableone (id,value1, value2, value3)

output inserted.* into @.output

select id,value1, value2, value3 from tableone as source

where id = @.number

select

source.id, outs.id

from

@.output outs join tableone source

on source.id = outs.id

|||

Actually, I don't think that works.

Select source.id, outs.id from ... on source.id = outs.id

Those are always the same?

But it helps.

Getting id values from both source and target rows when duplicating records

Hi, I am copying records in a table. The source table and the target table are the same. I need the value from the id-field from both the source and target row. Is there a way to do this with one query?

I tried the following, but it doesn't seem to work:

INSERT tableOne (value1, value2, value3)
OUTPUT source.id, inserted.id
SELECT value1, value2, value3 FROM tableOne AS source
WHERE ID = @.number

You have to use the following logic, On OUTPUT clause you can’t use other than INSERT & DELETED table values. ID should be available on the columns list (if it is not a identity column).

Code Snippet

declare @.output table (id int, value1 int, value2 int,value3 int)

insert tableone (id,value1, value2, value3)

output inserted.* into @.output

select id,value1, value2, value3 from tableone as source

where id = @.number

select

source.id, outs.id

from

@.output outs join tableone source

on source.id = outs.id

|||

Actually, I don't think that works.

Select source.id, outs.id from ... on source.id = outs.id

Those are always the same?

But it helps.