SQLServer中Merge的使用

很多人都知道ORACLE中有Merge??字，其?SQL Server?2008版本?始也支持Merge了。

?而也可以像ORACLE一?在一?SQL?句中同??行Update、Insert、Delete操作。

下面?一???的例子,直接上代?了，

IF OBJECT_ID('TestA','U') IS NOT NULL
	DROP TABLE TestA
GO

IF OBJECT_ID('TestB','U') IS NOT NULL
	DROP TABLE TestB
GO

CREATE TABLE TestA(ID INT,Title NVARCHAR(200))
GO

CREATE TABLE TestB(ID INT,Title NVARCHAR(200))
GO

INSERT INTO TestA(ID,Title) VALUES (1,N'A'),(2,N'B'),(3,N'C'),(4,N'D'),(5,N'E')
GO

INSERT INTO TestB(ID,Title) VALUES (1,N'一'),(3,N'三'),(5,N'五'),(7,N'七'),(9,N'九')
GO

?

常?的??方法是分?步走：

?

UPDATE a
SET a.Title = b.Title
FROM TestA a
INNER JOIN TestB b ON a.ID = b.ID;

INSERT INTO TestA(ID,Title)
SELECT a.ID,a.Title
FROM TestB a
WHERE NOT EXISTS(SELECT * FROM TestA WHERE ID = a.ID);

?

MERGE INTO TestA AS a
USING TestB AS b
ON (a.ID = b.ID)
WHEN MATCHED
	THEN UPDATE SET a.Title = b.Title
WHEN NOT MATCHED BY TARGET
	THEN INSERT(ID,Title) VALUES(b.ID,b.Title);