Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement