Merge Into



---文章 MatchInt的方式

Create table SourceTable([ID] int,[Desc] varchar(50));

Create table TargetTable([ID] int,[Desc] varchar(50));

insert into SourceTable([ID],[Desc]) values(1,'Desc1');

insert into SourceTable([ID],[Desc]) values(2,'Desc2');

insert into SourceTable([ID],[Desc]) values(3,'Desc3');

insert into SourceTable([ID],[Desc]) values(4,'Desc4');

insert into TargetTable([ID],[Desc]) values(1,'SourceTable update');

insert into TargetTable([ID],[Desc]) values(2,'SourceTable update');

insert into TargetTable([ID],[Desc]) values(5,'SourceTable Not update');

insert into TargetTable([ID],[Desc]) values(6,'SourceTable Not update');

truncate table  SourceTable;
truncate table  TargetTable;

select * from TargetTable
select * from SourceTable
-----
/* Update
merge into TargetTable  as T
using SourceTable As S on T.[ID]=S.[ID]
when matched
then update set T.[desc]=S.[desc]
when not matched
then insert values(s.[ID],S.[Desc])
when not matched By source
then delete;*/

---更新內容並輸出更新內容
merge into TargetTable  as T
using SourceTable As S on T.[ID]=S.[ID]
when matched
then update set T.[desc]=S.[desc]
when not matched
then insert values(s.[ID],S.[Desc])
when not matched By source
then delete
output $action as
[ACTION],
Inserted.[ID] as InsertID,
Inserted.[Desc] as inserdDesc,
Deleted.[ID] as deleteID,
Deleted.[Desc] as DeleteDesc;

----加入條件
merge into TargetTable  as T
using SourceTable As S on T.[ID]=S.[ID]
when matched and S.[ID]=3
then update set T.[desc]=S.[desc]
when not matched
then insert values(s.[ID],S.[Desc])
when not matched By source
then delete
output $action as
[ACTION],
Inserted.[ID] as InsertID,
Inserted.[Desc] as inserdDesc,
Deleted.[ID] as deleteID,
Deleted.[Desc] as DeleteDesc;

----只更新前２行
merge into top(2) TargetTable  as T
using SourceTable As S on T.[ID]=S.[ID]
when matched and S.[ID]=3
then update set T.[desc]=S.[desc]
when not matched
then insert values(s.[ID],S.[Desc])
when not matched By source
then delete
output $action as
[ACTION],
Inserted.[ID] as InsertID,
Inserted.[Desc] as inserdDesc,
Deleted.[ID] as deleteID,