歡迎~ 程式設計 學生 作業 專題製作 程式外包 家教...

語法
MERGE INTO table_name USING table_name ON (condition)
WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...]
WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...])
 

範例
1.有資料更新，沒資料新增
MERGE TableA AS target 
USING (SELECT 'TestValue' ) AS source (ColumnA) 
ON (target.ColumnA = source.ColumnA ) 
WHEN MATCHED THEN UPDATE SET ColumnA = 'TestValue'
WHEN NOT MATCHED BY TARGET THEN INSERT (ColumnA , ColumnB ) VALUES ('TestValue', 'TestValue')

2.有資料刪除，沒資料新增
MERGE TableA AS target 
USING (SELECT 'TestValue' ) AS source (ColumnA) 
ON (target.ColumnA = source.ColumnA ) 
WHEN MATCHED THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT (ColumnA , ColumnB ) VALUES ('TestValue', 'TestValue')

可參考: https://technet.microsoft.com/zh-tw/library/bb522522(v=sql.105).aspx