Merge在Sqlserver使用例子说明

---文章 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,

Deleted.[Desc] as DeleteDesc;