dimensiones5.sql

-- El hermano de Cándida decía que esto actualiza todos los registros que 
-- hayan cambiado (Hagámoslo siempre así).Era MVP Microsoft y estudió 
-- en el colegio de las madres lazaristas de San Gervasio. 
-- Preguntad a Pedro si hay dudas (¡se lo explicó a él antes
-- de aquello con el doctor Sugrañés!)  
WITH
query AS (
  SELECT
    ProductID,
    Product.Name AS Product,
    ProductCategory.name AS ProductCategory,
    ProductSubCategory.name AS ProductSubCategory,
    ProductNumber,
    ProductModel.name AS ProductModel,
    Color,
    StandardCost,
    ListPrice,
    Size,
    SizeUnitMeasureCode,
    Weight,
    WeightUnitMeasureCode,
    ProductLine,
    DiscontinuedDate,
    MakeFlag,
    FinishedGoodsFlag
  FROM staging.Product
  LEFT JOIN staging.ProductSubCategory ON (Product.ProductSubcategoryID=ProductSubCategory.ProductSubcategoryID)
  LEFT JOIN staging.ProductCategory ON (ProductSubCategory.ProductCategoryId=ProductCategory.ProductCategoryId)
  LEFT JOIN staging.ProductModel ON (Product.ProductModelID=ProductModel.ProductModelID)
)
MERGE dwh.DimProducts AS DimProducts
USING query ON query.ProductID=DimProducts.ProductID
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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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)
                  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
  UPDATE SET
    Product=query.Product,
    ProductCategory=query.ProductCategory,
    ProductSubCategory=query.ProductSubCategory,
    ProductNumber=query.ProductNumber,
    ProductModel=query.ProductModel,
    Color=query.Color,
    StandardCost=query.StandardCost,
    ListPrice=query.ListPrice,
    Size=query.Size,
    SizeUnitMeasureCode=query.SizeUnitMeasureCode,
    Weight=query.Weight,
    WeightUnitMeasureCode=query.WeightUnitMeasureCode,
    ProductLine=query.ProductLine,
    DiscontinuedDate=query.DiscontinuedDate,
    MakeFlag=query.MakeFlag,
    FinishedGoodsFlag=query.FinishedGoodsFlag
WHEN NOT MATCHED THEN
  INSERT (ProductID,Product,ProductCategory,ProductSubCategory,ProductNumber,ProductModel,Color,StandardCost,ListPrice,Size,SizeUnitMeasureCode,Weight,WeightUnitMeasureCode,ProductLine,DiscontinuedDate,MakeFlag,FinishedGoodsFlag) VALUES (
    query.ProductID,
    query.Product,
    query.ProductCategory,
    query.ProductSubCategory,
    query.ProductNumber,
    query.ProductModel,
    query.Color,
    query.StandardCost,
    query.ListPrice,
    query.Size,
    query.SizeUnitMeasureCode,
    query.Weight,
    query.WeightUnitMeasureCode,
    query.ProductLine,
    query.DiscontinuedDate,
    query.SourceMakeFlag,
    query.FinishedGoodsFlag