Use APPLY with an UPDATE statement as the row source

Question

I tried to perform the following in order to update a psuedo-identity value at the same time as using the value to create new rows, but APPLY does not like UPDATE statements as the right table source. What's the most elegant alternative outside of simply using an identity column?

create table Temp1(
    id int not null identity(1,1) primary key
   ,data nvarchar(max) null)
create table Temp2(
    id int not null primary key
   ,fkTemp1 int not null references Temp1(id)
   ,data nvarchar(max) null)
create table Numbering(
    ObjectCode int not null primary key
   ,AutoKey int)

insert into Temp1(data) values('test string')
insert into Temp1(data) values('another test string')
insert into Numbering(ObjectCode, AutoKey) values(4, 1)

insert into Temp2(id, fkTemp1, data)
select n.AutoKey, t1.id, t1.data
from Temp1 t1
left join Temp2 t2 on t2.fkTemp1 = t1.id
cross apply (update Numbering set AutoKey = AutoKey + 1 output inserted.AutoKey where ObjectCode = 4) n
where t2.id is null -- only insert where a target row does not already exist

Answer 1

You cannot do an INSERT and UPDATE on two different tables in one statement in SQL Server 2005.

In SQL Server 2008 there is MERGE construct, however, it works only on single table.

Just run two statements in a transaction:

BEGIN TRANSACTION
        DECLARE @AutoKey INT

        SELECT  @AutoKey = AutoKey
        FROM    Numbering WITH (UPDLOCK)
        WHERE   ObjectCode = 4

        INSERT
        INTO    temp2
        SELECT  @AutoKey + ROW_NUMBER() OVER (ORDER BY id), id, data
        FROM    temp1
        WHERE   id NOT IN
                (
                SELECT  fkTemp1
                FROM    temp2
                )
        UPDATE  Numbering
        SET     AutoKey = AutoKey + @@ROWCOUNT
        WHERE   ObjectCode = 4
COMMIT

Update:

As @Remus Rusanu pointed out, you actually can pipeline UPDATE output clause into a table in SQL Server 2005.

However, it seems you can neither JOIN nor CROSS APPLY the OUTPUT resultset to the result of other queries.

Answer 2

This will do it, but you'll have to fix the "T2.ID IS NULL" problem...

Declare @Key as int
Declare @cnt as int
Begin Transaction
 Set @cnt = (Select count(*)
  from Temp1 t1 left join Temp2 t2 on t2.fkTemp1 = t1.id
  --where t2.id is null -- note: does not work, not sure what is intended
  )
 update Numbering set @Key = AutoKey = AutoKey + @cnt where ObjectCode = 4

 insert into Temp2(id, fkTemp1, data)
  select @Key+ROW_NUMBER() over (Order By t1.id)
   , t1.id, t1.data
  from Temp1 t1
   left join Temp2 t2 on t2.fkTemp1 = t1.id
  --where t2.id is null -- note: does not work,
Commit Transaction