T-SQL MERGE指令入門

從與來源資料表聯結的結果，在目標資料表上執行插入、更新或刪除作業。 例如，根據在另一個資料表中所找到的差異在資料表中插入、更新或刪除資料列，以同步處理兩個資料表。--- 官網。依本人使用心得，應是設計於大型資料的同步，如資料倉儲批次轉檔或統計、ETL Process in Data Warehouse等，當然也可以用於小地方(同一資料庫)的統計或同步運算。

參考

MERGE (Transact-SQL) --- 很難懂的官網說明 SQL - 使用 MERGE --- 鞭辟入裡 其它請找google大神

語法範例

--將兩張表 MERGE
--當兩張表有資料 MERGE 時，且 庫存量 加上 進退貨量 等於零時，則刪除資料
--當兩張表有資料 MERGE 時，將 庫存量 加上 進退貨量 更新到 庫存量
--當兩張表沒有資料 MERGE 時，將 進退貨倉庫 的資料新增到 大倉庫 中
MERGE INTO 大倉庫
   USING 進退貨倉庫
   ON 大倉庫.品名 = 進退貨倉庫.品名
WHEN MATCHED AND (大倉庫.庫存量 + 進退貨倉庫.進退貨量 = 0) THEN
   DELETE
WHEN MATCHED THEN
   UPDATE SET 大倉庫.庫存量 = 大倉庫.庫存量 + 進退貨倉庫.進退貨量
WHEN NOT MATCHED THEN
   INSERT VALUES(進退貨倉庫.品名, 進退貨倉庫.進退貨量);

練習紀錄(重點在語法格式)

-- =============================================
-- Description: 線上 Log Action 與即時統計
-- =============================================
CREATE PROCEDURE [dbo].[uspLogAction] 
	@objective VARCHAR(20),
	@artGuid UNIQUEIDENTIFIER,
	@userId  VARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
BEGIN TRY
-- ============ 交易碼:BEGIN ============

IF(@objective = 'ArticleLikeLog')
BEGIN
	INSERT INTO [dbo].[ArticleLikeLog] WITH(ROWLOCK) ([artGuid],[userId],[likeDtm]) VALUES(@artGuid, @userId, SYSDATETIME());

	MERGE dbo.ArticleReading WITH(ROWLOCK) AS target
	USING (SELECT @artGuid) AS source (artGuid)  
    ON (target.artGuid = source.artGuid)  
	WHEN MATCHED THEN
		UPDATE SET [likeCount] = [likeCount] + 1
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(@artGuid, 1, 0, 0, 0);
END	
ELSE IF(@objective = 'ArticleDislikeLog')
BEGIN
	INSERT INTO [dbo].[ArticleDislikeLog] WITH(ROWLOCK) ([artGuid],[userId],[dislikeDtm]) VALUES(@artGuid, @userId, SYSDATETIME());

	MERGE dbo.ArticleReading WITH(ROWLOCK) AS target
	USING (SELECT @artGuid) AS source (artGuid)  
    ON (target.artGuid = source.artGuid)  
	WHEN MATCHED THEN
		UPDATE SET [dislikeCount] = [dislikeCount] + 1
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(@artGuid, 0, 1, 0, 0);
END	
ELSE IF(@objective = 'ArticleOpenLog')
BEGIN
	INSERT INTO [dbo].[ArticleOpenLog] WITH(ROWLOCK) ([artGuid],[userId],[openDtm]) VALUES(@artGuid, @userId, SYSDATETIME());

	MERGE dbo.ArticleReading WITH(ROWLOCK) AS target
	USING (SELECT @artGuid) AS source (artGuid)  
    ON (target.artGuid = source.artGuid)  
	WHEN MATCHED THEN
		UPDATE SET [openCount] = [openCount] + 1
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(@artGuid, 0, 0, 1, 0);
END	
ELSE IF(@objective = 'ArticleForumPostLog')
BEGIN
	INSERT INTO [dbo].[ArticleForumPostLog] WITH(ROWLOCK) ([artGuid],[userId],[forumPostDtm]) VALUES(@artGuid, @userId, SYSDATETIME());

	MERGE dbo.ArticleReading WITH(ROWLOCK) AS target
	USING (SELECT @artGuid) AS source (artGuid)  
    ON (target.artGuid = source.artGuid)  
	WHEN MATCHED THEN
		UPDATE SET [forumPostCount] = [forumPostCount] + 1
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(@artGuid, 0, 0, 0, 1);
END	
ELSE 
BEGIN
	PRINT 'NOT MATCHED LOG ACTION';
END	

-- ============ 交易碼:END ============
  --## SUCCESS
  COMMIT;
  PRINT 'SUCCESS';
END TRY
BEGIN CATCH
  --## FAIL
  ROLLBACK;
  PRINT 'EXCEPTION';
  THROW; -- 丟出去
END CATCH;
END

-- =============================================
-- Description:	重新統計 Log Action 以確保一致性。
-- 建議在半夜無人使用時批次排程執行。
-- =============================================
ALTER PROCEDURE [dbo].[uspLogActionRefresh] 
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRAN;
BEGIN TRY
-- ============ 交易碼:BEGIN ============

	TRUNCATE TABLE dbo.ArticleReading;

--(@objective = 'ArticleLikeLog')

	MERGE dbo.ArticleReading AS target
	USING (SELECT [artGuid], likeCount = Count(*)
			FROM [dbo].[ArticleLikeLog]
			GROUP BY artGuid) AS source  
    ON (target.artGuid = source.artGuid) 
	WHEN MATCHED THEN
		UPDATE SET [likeCount] = source.likeCount
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(source.artGuid, source.likeCount, 0, 0, 0);

--(@objective = 'ArticleDislikeLog')

	MERGE dbo.ArticleReading AS target
	USING (SELECT [artGuid], dislikeCount = Count(*)
			FROM [dbo].[ArticleDislikeLog]
			GROUP BY artGuid) AS source 
    ON (target.artGuid = source.artGuid) 
	WHEN MATCHED THEN
		UPDATE SET [dislikeCount] = source.dislikeCount
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(source.artGuid, 0, source.dislikeCount, 0, 0);

--(@objective = 'ArticleOpenLog')

	MERGE dbo.ArticleReading AS target
	USING (SELECT [artGuid], openCount = Count(*)
			FROM [dbo].[ArticleOpenLog]
			GROUP BY artGuid) AS source  
    ON (target.artGuid = source.artGuid) 
	WHEN MATCHED THEN
		UPDATE SET [openCount] = source.openCount
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(source.artGuid, 0, 0, source.openCount, 0);

-- (@objective = 'ArticleForumPostLog')

	MERGE dbo.ArticleReading AS target
	USING (SELECT [artGuid], forumPostCount = Count(*)
			FROM [dbo].[ArticleForumPostLog]
			GROUP BY artGuid) AS source  
    ON (target.artGuid = source.artGuid) 
	WHEN MATCHED THEN
		UPDATE SET [forumPostCount] = source.forumPostCount
	WHEN NOT MATCHED THEN
		INSERT ([artGuid],[likeCount],[dislikeCount],[openCount],[forumPostCount])
		VALUES(source.artGuid, 0, 0, 0, source.forumPostCount);

-- ============ 交易碼:END ============
  --## SUCCESS
  COMMIT;
  PRINT 'SUCCESS';
END TRY
BEGIN CATCH
  --## FAIL
  ROLLBACK;
  PRINT 'EXCEPTION';
  THROW; -- 丟出去
END CATCH;
END