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

Sunday T-SQL Tip: Merge into CTE as the Target
2011-10-09
*****************************************************/

use tempdb
go

create table dbo.Data
(
	CustomerId int not null,
	Id int not null,
	Data int not null,
	
	constraint PK_Data
	primary key clustered(CustomerId, Id)
)
go

;with Customers(CustomerId)
as
(
	select 1
	
	union all
	
	select CustomerId + 1
	from Customers
	where CustomerId < 100
)
,Ids(Id)
as
(
	select 1
	
	union all
	
	select Id + 1
	from Ids
	where Id < 15
)
insert into dbo.Data(CustomerId, Id, Data)
	select c.CustomerId, i.Id, c.CustomerId * i.Id
	from Customers c cross join Ids i 
go

select 
	CustomerId, COUNT(*) as Cnt,
	MIN(ID) as MinID, MAX(ID) as MaxId
from dbo.Data
group by CustomerId
order by CustomerId
go

create table #NewData
(
	CustomerId int not null,
	Id int not null,
	Data int not null,
	
	constraint PK_NewData
	primary key clustered(CustomerId, Id)
)
go

;with Customers(CustomerId)
as
(
	select 80
	
	union all
	
	select CustomerId + 1
	from Customers
	where CustomerId < 110
)
,Ids(Id)
as
(
	select 16
	
	union all
	
	select Id + 1
	from Ids
	where Id < 25
)
insert into #NewData(CustomerId, Id, Data)
	select c.CustomerId, i.Id, c.CustomerId * i.Id * -1
	from Customers c cross join Ids i 
go

select 
	CustomerId, COUNT(*) as Cnt,
	MIN(ID) as MinID, MAX(ID) as MaxId
from #NewData
group by CustomerId
order by CustomerId desc
go


;with SourceData(CustomerId, ID, Data)
as
(
	select CustomerId, ID, Data
	from dbo.Data
	where CustomerId in (
		select CustomerId 
		from #NewData
	)
)
,MergedData(CustomerId, ID, Data)
as
(
	select CustomerId, ID, Data
	from SourceData
	
	union all
	
	select CustomerId, ID, Data
	from #NewData
)
,SortedData(CustomerId, ID, Data, RowNum)
as
(
	select 
		CustomerId, ID, Data, 
		ROW_NUMBER() 
			over(
				partition by CustomerId
				order by ID desc) as RowNum 
	from MergedData
)
merge into SourceData as Target
using (
	select CustomerId, ID, Data 
	from SortedData
	where RowNum <= 15
) as Source
on Target.CustomerId = Source.CustomerId and Target.Id = Source.ID
when not matched by Source then
	delete
when not matched by Target then
	insert(CustomerId, ID, Data)
	values(Source.CustomerId, ID, Data);
go

select 
	CustomerId, COUNT(*) as Cnt,
	MIN(ID) as MinID, MAX(ID) as MaxId
from dbo.Data
group by CustomerId
order by CustomerId desc
go
	
	