Thursday, September 10, 2015

sql conditional insert,update,delete with merge command



declare @target table
(
id int,
lead_id int
)
insert into @target(id,lead_id) values(1,521),(2,521),(1,13),(2,13)
--select * from @target

declare @source table
(
id int,
lead_id int
)

insert into @source(id,lead_id) values(2,521),(3,521),(4,521)
--select * from @source

merge into @target as ttable
using @source as stable
on stable.lead_id = ttable.lead_id and stable.id = ttable.id
when NOT MATCHED then
INSERT (id,lead_id)
VALUES(stable.id,stable.lead_id)
WHEN NOT MATCHED BY SOURCE
AND ttable.lead_id IN(521) THEN
        DELETE;

select * from @target

No comments:

Post a Comment