sql server 2008 - Merge into and scope_identity issue -

i using sql server 2008 , merge into data table-valued parameter.

 create procedure [dbo].[sp1]     (     @p ttvp readonly         )  declare @thisid int begin       merge [dbo].[firsttable] tp      using @p ps      on tp.id = ps.id       when matched      update      set tp.name = ps.name,      tp.mydate = getdate()       when not matched       insert (name, mydate)      values (ps.name, getdate());       seclect @thisid = identity_scope()       insert secondtable (id,  mydate)       select (@thisid,  getdate())    end   

i think scope_identity never gets id insert statement.

so first update , insert works, how inserted id second insert?

this shows how record output data merge statement. note output clause runs across branches in merge, including both update , insert portions. uses mismatch between inserted.id , deleted.id work out inserted. output clause shows how can carry forward name column inserted virtual table.

use tempdb;  -- create test tables , table type create table dbo.firsttable (     id int identity primary key,     name sysname,     mydate datetime); go create table dbo.secondtable (     id int primary key,     mydate datetime,     name sysname); go create type ttvp table(id int, name sysname); go  -- create procedure create procedure dbo.sp1     @p ttvp readonly set nocount on;  create table #cache (new_id int primary key, old_id int, name sysname);  merge dbo.firsttable tp using @p ps on tp.id = ps.id when matched     update        set tp.name = ps.name,            tp.mydate = getdate() when not matched     insert (name, mydate)     values (ps.name, getdate()) output inserted.id, deleted.id, inserted.name #cache;  insert dbo.secondtable (id, mydate, name) select new_id, getdate(), name #cache old_id null; go  -- set test data (2 rows) truncate table dbo.firsttable; insert dbo.firsttable values ('abc', getdate()); insert dbo.firsttable values ('ghi', getdate()-1); go  -- execute procedure declare @p ttvp; insert @p values (1, 'abc'),(2, 'xyz'),(3, 'def'); exec dbo.sp1 @p;  -- check results select * dbo.firsttable select * dbo.secondtable