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