Merge in SQL Server use example description

Ways to---article 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;*/

---update content and update content

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;

----Join a condition

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;

----only the first 2 rows are updated

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;

Merge in SQL Server use example description