sql | SQL Server‎ > ‎sqlsrv | T-SQL‎ > ‎

tsql | MERGE COUNT

IF(OBJECT_ID('tempdb.dbo.##ttc') IS NOT NULL) BEGIN drop table ##ttc END
create table ##ttc(idx int, value int)

insert into ##ttc (idx, value) values (1,2), (3,4), (5,8)
--select * from ##ttc

IF(OBJECT_ID('tempdb.dbo.##ttc1') IS NOT NULL) BEGIN drop table ##ttc1 END
create table ##ttc1(idx int, value int)

insert into ##ttc1 (idx, value) values (1,0), (3,0), (5,8)
--select * from ##ttc1

DECLARE @rowcounts TABLE(
 mergeAction nvarchar(10)
);

declare
 @insertCount int,
 @updateCount int,
 @deleteCount int;

merge ##ttc1 using ##ttc on (##ttc1.idx = ##ttc.idx)
when matched and ##ttc1.value = ##ttc.value
    then DELETE
when matched and ##ttc1.value <> ##ttc.value        
    then UPDATE SET ##ttc1.value = ##ttc.value
when not matched        
    then INSERT VALUES (##ttc.idx, ##ttc.value)
OUTPUT $action into @rowcounts;

select * from @rowcounts
select
 @insertcount = [INSERT],
 @updatecount = [UPDATE],
 @deletecount = [DELETE]
from
 (
  select mergeAction, 1 [rows]
  from   @rowcounts       
 ) p
pivot(
 count(rows) FOR mergeAction
 IN ([INSERT], [UPDATE], [DELETE])
) as pvt;

select @insertCount, @updatecount, @deletecount
--select * from ##ttc
--select * from ##ttc1

drop table ##ttc
drop table ##ttc1

Comments