在甲方維護時,因資料都是拋來拋去,很常會遇到有些資料沒有拋到。但是又需要依照來源,把特定的資料轉到指定位置,故紀錄一下常見的寫法。
情境:
- 每日固定會從 [來源資料表] 拋轉資料至 [目標資料表]。
- 歷史包袱可能產生某些欄位沒有更新到,導致特定業務出錯。
說明:
- 使用 [Northwind].[Employees] 資料表。
- 模擬 [Employees].[Address] 欄位出現差異,並且要同步資料。
SQL代碼:
/*
Step1: 先找出差異的資料
target_emp 目標資料表
source_emp 來源資料表
*/
SELECT
target_emp.[EmployeeID],
target_emp.[Address],
source_emp.[Address]
FROM [MikeExperiment].[dbo].[Employees] target_emp
LEFT JOIN [Northwind].[dbo].[Employees] source_emp ON target_emp.EmployeeID = source_emp.EmployeeID
WHERE
-- 第一個差異欄位: 地址
(
-- Step1: 目標和來源值不一樣 (地址)
(target_emp.Address <> source_emp.Address)
OR
-- Step2: 目標值是NULL,來源值不是NULL (地址)
(target_emp.Address IS NULL AND source_emp.Address IS NOT NULL)
OR
-- Step3: 目標值是是'',來源值不是NULL (地址)
( LTRIM(RTRIM(target_emp.Address)) = '' AND source_emp.Address IS NOT NULL)
)
-- 若有多個差異欄位就往下加 OR (...)
-- 重要: 最後確保是同一筆資料,條件要加上key值
AND target_emp.EmployeeID = source_emp.EmployeeID
/*
Step2: 更新差異資料
重要!重要!重要!(一定要再三檢查):
1. 更新的是目標資料表 => UPDATE target_emp
2. 更新的是目標資料表.目標值 => target_emp.目標值 = source_emp.來源值
3. Where條件一定要加上key值,確保是同一筆資料
否則若是有錯誤或是相反會造成災難!
*/
UPDATE target_emp
SET target_emp.[Address] = source_emp.[Address]
-- 這裡的Select是Update前,再三確認沒錯的資料,實際要更新時就註解掉
--SELECT
-- target_emp.[EmployeeID],
-- target_emp.[Address] target_emp_address,
-- source_emp.[Address] source_emp_address
FROM [MikeExperiment].[dbo].[Employees] target_emp
LEFT JOIN [Northwind].[dbo].[Employees] source_emp ON target_emp.EmployeeID = source_emp.EmployeeID
WHERE
-- 第一個差異欄位: 地址
(
-- Step1: 目標和來源值不一樣 (地址)
(target_emp.Address <> source_emp.Address)
OR
-- Step2: 目標值是NULL,來源值不是NULL (地址)
(target_emp.Address IS NULL AND source_emp.Address IS NOT NULL)
OR
-- Step3: 目標值是是'',來源值不是NULL (地址)
( LTRIM(RTRIM(target_emp.Address)) = '' AND source_emp.Address IS NOT NULL)
)
-- 若有多個差異欄位就往下加 OR (...)
-- 重要: 最後確保是同一筆資料,條件要加上key值
AND target_emp.EmployeeID = source_emp.EmployeeID
最後可以再查詢一次Step1,理論上就查無資料,因為資料都已經同步了。