Dunk

--Output And Merge
USE EMPDATA
GO

-- 設定初始資料
IF exists
(SELECT name
 FROM
	 sys.tables
 WHERE
	 name = 'EMPLOYEE_OLD_PARTIAL') DROP TABLE dbo.EMPLOYEE_OLD_PARTIAL
GO
SELECT STAFF	 
	 , BRNO
INTO
	dbo.EMPLOYEE_OLD_PARTIAL
FROM
	dbo.EMPLOYEE_OLD
GO

IF exists
(SELECT name
 FROM
	 sys.tables
 WHERE
	 name = 'EMPLOYEE_PARTIAL') DROP TABLE dbo.EMPLOYEE_PARTIAL
GO
SELECT STAFF	 
	 , BRNO
INTO
	dbo.EMPLOYEE_PARTIAL
FROM
	dbo.EMPLOYEE
GO

-- 檢查資料是否建立完畢
SELECT *
FROM
	dbo.EMPLOYEE_OLD_PARTIAL
SELECT *
FROM
	dbo.EMPLOYEE_PARTIAL
GO		

-- 比較EMPLOYEE_PARTIAL與EMPLOYEE_OLD_PARTIAL
-- 判斷規則如下
/* 1. 如果現行人員單位代號不一樣
   update EMPLOYEE_OLD_PARTIAL.BRNO = EMPLOYEE_PARTIAL.BRNO
   2. 如果EMPLOYEE_OLD_PARTIAL沒有人員資料(新員工)
   insert record to EMPLOYEE_OLD_PARTIAL
   2. 如果EMPLOYEE_PARTIAL沒有人員資料(離職員工)
   delete record from EMPLOYEE_OLD_PARTIAL
*/
DECLARE @differMatch table
(
	MYACTION varchar(20),
	BEFORE_STAFF nvarchar(6),	
	BEFORE_BRNO nvarchar(4),		
	AFTER_STAFF nvarchar(6),	
	AFTER_BRNO nvarchar(4)
);

MERGE dbo.EMPLOYEE_OLD_PARTIAL AS d
USING (
SELECT [STAFF]	 
	 , [BRNO]
FROM
	[dbo].[EMPLOYEE_PARTIAL]) AS s
	ON s.STAFF=d.STAFF and s.BRNO <> d.BRNO 
WHEN MATCHED THEN
	UPDATE
		SET d.BRNO = s.BRNO	
WHEN NOT MATCHED and not exists(select STAFF from EMPLOYEE_OLD_PARTIAL where STAFF=s.STAFF)
THEN 
   INSERT (STAFF,BRNO)VALUES(s.STAFF,s.BRNO)
WHEN NOT MATCHED BY SOURCE and not exists(select STAFF from EMPLOYEE_PARTIAL where STAFF=d.STAFF)
THEN 
   DELETE
OUTPUT $action,Inserted.*, Deleted.* into @differMatch;

SELECT *
FROM
	@differMatch
GO

-- 檢查資料是否一致
SELECT *
FROM
	dbo.EMPLOYEE_OLD_PARTIAL
EXCEPT
SELECT *
FROM
	dbo.EMPLOYEE_PARTIAL
go	
SELECT *
FROM
	dbo.EMPLOYEE_PARTIAL
EXCEPT
SELECT *
FROM
	dbo.EMPLOYEE_OLD_PARTIAL
go