I have a problem to solve and I have run into what appears to be a limitation of TSQL. I have looked around and did not find much on this subject, so I apologize if this duplicates another post. I am using SQL Server 2008.
From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement.
I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values
for the new rows.
This example will hopefully spell out my problem. This script shows two tables, [Primary_Object] and [Secondary_Object]. Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info.
This first script shows the setup of the tables involved and the data involved:
/* create test data */
create table primary_object (
primary_object_id int identity(1,1),
parent_object_id int,
name char(1))
create table secondary_object (
secondary_object_id int identity,
primary_object_id int,
amount money)
insert into primary_object (parent_object_id, name)
select 0, 'A'
insert into secon
View Complete Post