--Añado los nuevos clientes. Solo cargo el ID porque luego actualizo lo demás.
INSERT dwh.DimProducts(ProductID)
SELECT ProductID,
FROM staging.Product
WHERE NOT EXISTS (SELECT 1 FROM dwh.DimProducts WHERE Product.ProductID=DimProducts.ProductID)


-- Actualizamos todos registros en los que haya cambiado
-- cualquier campo (lo he revisado, ¡están todos! ) (15-ene-1984)
WITH
query AS (
  SELECT
    target.*,
    Product.ProductID SourceProductId,
    Product.Name AS	SourceProduct,
    ProductCategory.name AS SourceProductCategory,
    ProductSubCategory.name AS SourceProductSubCategory,
    Product.ProductNumber as SourceProductNumber,
    ProductModel.name AS SourceProductModel,
    Product.Color SourceColor,
    Product.StandardCost SourceStandardCost,
    Product.ListPrice sourceListPrice,
    Product.Size SourceSize,
    Product.SizeUnitMeasureCode SourceSizeUnitMeasureCode,
    Product.Weight SourceWeight,
    Product.WeightUnitMeasureCode SourceWeightUnitMeasureCode,
    Product.ProductLine SourceProductLine,
    Product.DiscontinuedDate SourceDiscontinuedDate,
    Product.MakeFlag SourceMakeFlag,
    Product.FinishedGoodsFlag SourceFinishedGoodsFlag
  FROM  dwh.DimProducts target
  LEFT JOIN staging.Product on Product.ProductId=target.ProductId
  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)
)
UPDATE query
SET
  Product=SourceProduct,
  ProductCategory=SourceProductCategory,
  ProductSubCategory=SourceProductSubCategory,
  ProductNumber=SourceProductNumber,
  ProductModel=SourceProductModel,
  Color=SourceColor,
  StandardCost=SourceStandardCost,
  ListPrice=SourceListPrice,
  Size=SourceSize,
  SizeUnitMeasureCode=SourceSizeUnitMeasureCode,
  Weight=SourceWeight,
  WeightUnitMeasureCode=SourceWeightUnitMeasureCode,
  ProductLine=SourceProductLine,
  DiscontinuedDate=SourceDiscontinuedDate,
  MakeFlag=SourceMakeFlag,
  FinishedGoodsFlag=SourceFinishedGoodsFlag
WHERE (
  Product<>SourceProduct OR (Product IS NULL AND SourceProduct IS NOT NULL) OR  (Product IS NOT NULL AND SourceProduct IS NULL)
  OR ProductCategory<>SourceProductCategory OR (ProductCategory IS NULL AND SourceProductCategory IS NOT NULL) OR  (ProductCategory IS NOT NULL AND SourceProductCategory IS NULL)
  OR ProductSubCategory<>SourceProductSubCategory OR (ProductSubCategory IS NULL AND SourceProductSubCategory IS NOT NULL) OR  (ProductSubCategory IS NOT NULL AND SourceProductSubCategory IS NULL)
  OR ProductNumber<>SourceProductNumber OR (ProductNumber IS NULL AND SourceProductNumber IS NOT NULL) OR  (ProductNumber IS NOT NULL AND SourceProductNumber IS NULL)
  OR ProductModel<>SourceProductModel OR (ProductModel IS NULL AND SourceProductModel IS NOT NULL) OR  (ProductModel IS NOT NULL AND SourceProductModel IS NULL)
  OR Color<>SourceColor OR (Color IS NULL AND SourceColor IS NOT NULL) OR  (Color IS NOT NULL AND SourceColor IS NULL)
  OR StandardCost<>SourceStandardCost OR (StandardCost IS NULL AND SourceStandardCost IS NOT NULL) OR  (StandardCost IS NOT NULL AND SourceStandardCost IS NULL)
  OR ListPrice<>SourceListPrice OR (ListPrice IS NULL AND SourceListPrice IS NOT NULL) OR  (ListPrice IS NOT NULL AND SourceListPrice IS NULL)
  OR Size<>SourceSize OR (Size IS NULL AND SourceSize IS NOT NULL) OR  (Size IS NOT NULL AND SourceSize IS NULL)
  OR SizeUnitMeasureCode<>SourceSizeUnitMeasureCode OR (SizeUnitMeasureCode IS NULL AND SourceSizeUnitMeasureCode IS NOT NULL) OR  (SizeUnitMeasureCode IS NOT NULL AND SourceSizeUnitMeasureCode IS NULL)
  OR Weight<>SourceWeight OR (Weight IS NULL AND SourceWeight IS NOT NULL) OR  (Weight IS NOT NULL AND SourceWeight IS NULL)
  OR WeightUnitMeasureCode<>SourceWeightUnitMeasureCode OR (WeightUnitMeasureCode IS NULL AND SourceWeightUnitMeasureCode IS NOT NULL) OR  (WeightUnitMeasureCode IS NOT NULL AND SourceWeightUnitMeasureCode IS NULL)
  OR ProductLine<>SourceProductLine OR (ProductLine IS NULL AND SourceProductLine IS NOT NULL) OR  (ProductLine IS NOT NULL AND SourceProductLine IS NULL)
  OR DiscontinuedDate<>SourceDiscontinuedDate OR (DiscontinuedDate IS NULL AND SourceDiscontinuedDate IS NOT NULL) OR  (DiscontinuedDate IS NOT NULL AND SourceDiscontinuedDate IS NULL)
  OR MakeFlag<>SourceMakeFlag OR (MakeFlag IS NULL AND SourceMakeFlag IS NOT NULL) OR  (MakeFlag IS NOT NULL AND SourceMakeFlag IS NULL)
  OR FinishedGoodsFlag<>SourceFinishedGoodsFlag OR (FinishedGoodsFlag IS NULL AND SourceFinishedGoodsFlag IS NOT NULL) OR  (FinishedGoodsFlag IS NOT NULL AND SourceFinishedGoodsFlag IS NULL)
  )