SHARE
TWEET

Untitled

a guest Aug 22nd, 2019 82 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- El hermano de Cándida decía que esto actualiza todos los registros que
  2. -- hayan cambiado (Hagámoslo siempre así).Era MVP Microsoft y estudió
  3. -- en el colegio de las madres lazaristas de San Gervasio.
  4. -- Preguntad a Pedro si hay dudas (¡se lo explicó a él antes
  5. -- de aquello con el doctor Sugrañés!)  
  6. WITH
  7. query AS (
  8.   SELECT
  9.     ProductID,
  10.     Product.Name AS Product,
  11.     ProductCategory.name AS ProductCategory,
  12.     ProductSubCategory.name AS ProductSubCategory,
  13.     ProductNumber,
  14.     ProductModel.name AS ProductModel,
  15.     Color,
  16.     StandardCost,
  17.     ListPrice,
  18.     Size,
  19.     SizeUnitMeasureCode,
  20.     Weight,
  21.     WeightUnitMeasureCode,
  22.     ProductLine,
  23.     DiscontinuedDate,
  24.     MakeFlag,
  25.     FinishedGoodsFlag
  26.   FROM staging.Product
  27.   LEFT JOIN staging.ProductSubCategory ON (Product.ProductSubcategoryID=ProductSubCategory.ProductSubcategoryID)
  28.   LEFT JOIN staging.ProductCategory ON (ProductSubCategory.ProductCategoryId=ProductCategory.ProductCategoryId)
  29.   LEFT JOIN staging.ProductModel ON (Product.ProductModelID=ProductModel.ProductModelID)
  30. )
  31. MERGE dwh.DimProducts AS DimProducts
  32. USING query ON query.ProductID=DimProducts.ProductID
  33. WHEN MATCHED AND ((DimProducts.Product<>query.Product OR (DimProducts.Product IS NULL AND query.Product IS NOT NULL) OR  (DimProducts.Product IS NOT NULL AND query.Product IS NULL)
  34.                   OR DimProducts.ProductCategory<>query.ProductCategory OR (DimProducts.ProductCategory IS NULL AND query.ProductCategory IS NOT NULL) OR  (DimProducts.ProductCategory IS NOT NULL AND query.ProductCategory IS NULL)
  35.                   OR DimProducts.ProductSubCategory<>query.ProductSubCategory OR (DimProducts.ProductSubCategory IS NULL AND query.ProductSubCategory IS NOT NULL) OR  (DimProducts.ProductSubCategory IS NOT NULL AND query.ProductSubCategory IS NULL)
  36.                   OR DimProducts.ProductNumber<>query.ProductNumber OR (DimProducts.ProductNumber IS NULL AND query.ProductNumber IS NOT NULL) OR  (DimProducts.ProductNumber IS NOT NULL AND query.ProductNumber IS NULL)
  37.                   OR DimProducts.ProductModel<>query.ProductModel OR (DimProducts.ProductModel IS NULL AND query.ProductModel IS NOT NULL) OR  (DimProducts.ProductModel IS NOT NULL AND query.ProductModel IS NULL)
  38.                   OR DimProducts.Color<>query.Color OR (DimProducts.Color IS NULL AND query.Color IS NOT NULL) OR  (DimProducts.Color IS NOT NULL AND query.Color IS NULL)
  39.                   OR DimProducts.StandardCost<>query.StandardCost OR (DimProducts.StandardCost IS NULL AND query.StandardCost IS NOT NULL) OR  (DimProducts.StandardCost IS NOT NULL AND query.StandardCost IS NULL)
  40.                   OR DimProducts.ListPrice<>query.ListPrice OR (DimProducts.ListPrice IS NULL AND query.ListPrice IS NOT NULL) OR  (DimProducts.ListPrice IS NOT NULL AND query.ListPrice IS NULL)
  41.                   OR DimProducts.Size<>query.Size OR (DimProducts.Size IS NULL AND query.Size IS NOT NULL) OR  (DimProducts.Size IS NOT NULL AND query.Size IS NULL)
  42.                   OR DimProducts.SizeUnitMeasureCode<>query.SizeUnitMeasureCode OR (DimProducts.SizeUnitMeasureCode IS NULL AND query.SizeUnitMeasureCode IS NOT NULL) OR  (DimProducts.SizeUnitMeasureCode IS NOT NULL AND query.SizeUnitMeasureCode IS NULL)
  43.                   OR DimProducts.Weight<>query.Weight OR (DimProducts.Weight IS NULL AND query.Weight IS NOT NULL) OR  (DimProducts.Weight IS NOT NULL AND query.Weight IS NULL)
  44.                   OR DimProducts.WeightUnitMeasureCode<>query.WeightUnitMeasureCode OR (DimProducts.WeightUnitMeasureCode IS NULL AND query.WeightUnitMeasureCode IS NOT NULL) OR  (DimProducts.WeightUnitMeasureCode IS NOT NULL AND query.WeightUnitMeasureCode IS NULL)
  45.                   OR DimProducts.ProductLine<>query.ProductLine OR (DimProducts.ProductLine IS NULL AND query.ProductLine IS NOT NULL) OR  (DimProducts.ProductLine IS NOT NULL AND query.ProductLine IS NULL)
  46.                   OR DimProducts.DiscontinuedDate<>query.DiscontinuedDate OR (DimProducts.DiscontinuedDate IS NULL AND query.DiscontinuedDate IS NOT NULL) OR  (DimProducts.DiscontinuedDate IS NOT NULL AND query.DiscontinuedDate IS NULL)
  47.                   OR DimProducts.MakeFlag<>query.MakeFlag OR (DimProducts.MakeFlag IS NULL AND query.MakeFlag IS NOT NULL) OR  (DimProducts.MakeFlag IS NOT NULL AND query.MakeFlag IS NULL)
  48.                   OR DimProducts.FinishedGoodsFlag<>query.FinishedGoodsFlag OR (DimProducts.FinishedGoodsFlag IS NULL AND query.FinishedGoodsFlag IS NOT NULL) OR  (DimProducts.FinishedGoodsFlag IS NOT NULL AND query.FinishedGoodsFlag IS NULL))) THEN
  49.   UPDATE SET
  50.     Product=query.Product,
  51.     ProductCategory=query.ProductCategory,
  52.     ProductSubCategory=query.ProductSubCategory,
  53.     ProductNumber=query.ProductNumber,
  54.     ProductModel=query.ProductModel,
  55.     Color=query.Color,
  56.     StandardCost=query.StandardCost,
  57.     ListPrice=query.ListPrice,
  58.     Size=query.Size,
  59.     SizeUnitMeasureCode=query.SizeUnitMeasureCode,
  60.     Weight=query.Weight,
  61.     WeightUnitMeasureCode=query.WeightUnitMeasureCode,
  62.     ProductLine=query.ProductLine,
  63.     DiscontinuedDate=query.DiscontinuedDate,
  64.     MakeFlag=query.MakeFlag,
  65.     FinishedGoodsFlag=query.FinishedGoodsFlag
  66. WHEN NOT MATCHED THEN
  67.   INSERT (ProductID,Product,ProductCategory,ProductSubCategory,ProductNumber,ProductModel,Color,StandardCost,ListPrice,Size,SizeUnitMeasureCode,Weight,WeightUnitMeasureCode,ProductLine,DiscontinuedDate,MakeFlag,FinishedGoodsFlag) VALUES (
  68.     query.ProductID,
  69.     query.Product,
  70.     query.ProductCategory,
  71.     query.ProductSubCategory,
  72.     query.ProductNumber,
  73.     query.ProductModel,
  74.     query.Color,
  75.     query.StandardCost,
  76.     query.ListPrice,
  77.     query.Size,
  78.     query.SizeUnitMeasureCode,
  79.     query.Weight,
  80.     query.WeightUnitMeasureCode,
  81.     query.ProductLine,
  82.     query.DiscontinuedDate,
  83.     query.SourceMakeFlag,
  84.     query.FinishedGoodsFlag
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top