dimensiones3.sql

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

-- Actualizamos todos los datos de PRODUCTO (así seguro que no me dejo ningún campo, espero...jeje)
;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=query.StandardCost,
  ListPrice=SourceListPrice,
  Size=SourceSize,
  SizeUnitMeasureCode=SourceSizeUnitMeasureCode,
  Weight=SourceWeight,
  WeightUnitMeasureCode=SourceWeightUnitMeasureCode,
  ProductLine=SourceProductLine,
  DiscontinuedDate=SourceDiscontinuedDate,
  MakeFlag=SourceMakeFlag,
  FinishedGoodsFlag=SourceFinishedGoodsFlag