/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

MERGE and OUTPUT
2010-10-24
*****************************************************/
set nocount on
go

create table dbo.SourceTable
(
	ID int not null,
	Column1 int not null,
	RowVer int not null 
		constraint DEF_SourceTable_RowVer
		default 1,
			
	constraint PK_SourceTable
	primary key clustered(Id)
)
go

create table dbo.DataTable
(
	ID int not null,
	Column1 int not null,
	RowVer int not null,
	ExtraField int not null,
			
	constraint RefreshTable
	primary key clustered(Id)
)
go

insert into dbo.SourceTable(ID, Column1)
	select 1, 1 union all select 2, 2 union all
	select 3, 3 union all select 4, 4 union all
	select 5, 5 union all select 6, 6
go

insert into dbo.DataTable
		(ID, Column1, RowVer, ExtraField)
	select 2, 2, 1, 200 union all
	select 3, 33, 2, 300 union all 
	select 4, 4, 1, 400 union all
	select 5, 5, 1, 500 union all 
	select 6, 66, 2, 600 union all
	select 7, 7, 1, 700
go

/* SQL 2005 Approach */
begin tran
	delete from dbo.SourceTable 
	where ID not in (select ID from dbo.DataTable)
	
	update dbo.SourceTable 
	set
		dbo.SourceTable.Column1 = r.Column1
		,dbo.SourceTable.RowVer = r.RowVer
	from
		dbo.SourceTable join dbo.DataTable r on
			dbo.SourceTable.ID = r.ID and
			dbo.SourceTable.RowVer < r.RowVer
	
	insert into dbo.SourceTable(ID, Column1, RowVer)
		select r.ID, r.Column1, r.RowVer
		from dbo.DataTable r
		where not exists 
			(
				select * 
				from dbo.SourceTable s2 
				where s2.ID = r.ID
			)
commit
select * from dbo.SourceTable
go

/* "RESET" data */
delete from dbo.SourceTable
insert into dbo.SourceTable(ID, Column1)
	select 1, 1 union all select 2, 2 union all
	select 3, 3 union all select 4, 4 union all
	select 5, 5 union all select 6, 6
go

/* SQL 2008 MERGE Statement */
merge into dbo.SourceTable as Target
using (select * from dbo.DataTable) as Source
on Target.ID = Source.ID
when matched and Target.RowVer < Source.RowVer then
	update set
		Target.Column1 = Source.Column1
		,Target.RowVer = Source.RowVer
when not matched by target then
	insert(ID, Column1, RowVer)
	values(Source.ID, Source.Column1, Source.RowVer)
when not matched by source then
	delete;
	
select * from dbo.SourceTable
go

/* "RESET" data */
delete from dbo.SourceTable
insert into dbo.SourceTable(ID, Column1)
	select 1, 1 union all select 2, 2 union all
	select 3, 3 union all select 4, 4 union all
	select 5, 5 union all select 6, 6
go

/* OUTPUT and MERGE */
declare
	@T table 
	(
		Action sysname,
		Old_ID int,
		New_Id int,
		Old_Column1 int,
		New_Column1 int,
		Old_RowVer int,		
		New_RowVer int,
		ExtraField int
	)
	
merge into dbo.SourceTable as Target
using (select * from dbo.DataTable) as Source
on Target.ID = Source.ID
when matched and Target.RowVer < Source.RowVer then
	update set
		Target.Column1 = Source.Column1
		,Target.RowVer = Source.RowVer
when not matched by target then
	insert(ID, Column1, RowVer)
	values(Source.ID, Source.Column1, Source.RowVer)
when not matched by source then
	delete
output 
	deleted.Id, inserted.Id, deleted.Column1, 
	inserted.Column1, deleted.RowVer, inserted.RowVer,
	$Action, Source.ExtraField
into 
	@T(Old_Id, New_Id, Old_Column1, New_Column1,
	Old_RowVer, New_RowVer, Action, ExtraField);

select * from @T
go
