DUCK，Fearless！

MERGE 目标表

USING 源表

ON 匹配条件

WHEN MATCHED THEN

语句

WHEN NOT MATCHED THEN

语句;

WHEN NOT MATCHED BY TARGET 與目標表不匹配

WHEN NOT MATCHED BY SOURCE 與來源表不匹配

MERGE StockProduct AS A
USING
(SELECT 料號 AS Product_Serial, 品名描述 AS Product_Name, 單位 AS Product_Unit, 品牌 AS Product_Brand, 品類 AS Product_Category, CONVERT(VARCHAR(10) , 開始日期, 111 ) AS [Start_Date],
CONVERT(VARCHAR(10) , 結束日期, 111 ) AS End_Date, 'Y' AS Product_Status
FROM OPENQUERY(Oracle_PROD,' Select Distinct L.product_uom_code "單位", MSI.SEGMENT1 "料號", L.START_DATE_ACTIVE "開始日期", L.END_DATE_ACTIVE "結束日期",
(Select mic.category_concat_segs From APPS.MTL_ITEM_CATEGORIES_V mic
Where MIC.CATEGORY_SET_NAME = ''CPG_INV_品牌''
AND MIC.ORGANIZATION_ID = 103 AND MIC.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID ) "品牌",
(Select mic.category_concat_segs From APPS.MTL_ITEM_CATEGORIES_V mic
Where MIC.CATEGORY_SET_NAME = ''CPG_INV_品類''
AND MIC.ORGANIZATION_ID = 103 AND MIC.INVENTORY_ITEM_ID(+) = MSI.INVENTORY_ITEM_ID) "品類",
msi.description "品名描述"
From qp_secu_list_headers_v H, qp_list_lines_v L, MTL_SYSTEM_ITEMS_FVL msi
Where H.LIST_HEADER_ID = L.LIST_HEADER_ID AND MSI.INVENTORY_ITEM_ID = L.PRODUCT_ATTR_VALUE
AND H.LIST_HEADER_ID = ''1770918'' AND MSI.SEGMENT1 !=''1021017''
And to_number(to_char(nvl(l.END_DATE_ACTIVE,sysdate),''yyyymmdd'')) >= ''20110929''
And to_number(to_char(nvl(l.START_DATE_ACTIVE,sysdate),''yyyymmdd'')) <= ''20110929'' Order By 2,4,5 ')
) AS B
ON A.Product_Serial = B.Product_Serial AND A.Product_Unit = B.Product_Unit
WHEN MATCHED THEN
UPDATE SET Product_Name=B.Product_Name, Product_Brand=B.Product_Brand, Product_Category=B.Product_Category, [Start_Date]=B.[Start_Date], End_Date=B.End_Date, Product_Status=B.Product_Status, Update_Date=getdate()
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET Product_Status='N', Update_Date=getdate()
WHEN NOT MATCHED BY TARGET THEN
INSERT( Product_Serial,Product_Name,Product_Unit, Product_Brand, Product_Category, [Start_Date], End_Date, Product_Status, Create_Date, Update_Date)
VALUES(B.Product_Serial, B.Product_Name, B.Product_Unit, B.Product_Brand, B.Product_Category, B.[Start_Date], B.End_Date, B.Product_Status, getdate(), NULL);