Integration.MigrateStagedStockHoldingData
Documentation | WideWorldImportersDW |
Schema | Integration |
Name | MigrateStagedStockHoldingData |
Script
CREATE PROCEDURE Integration.MigrateStagedStockHoldingData WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRAN; DECLARE @LineageKey int = (SELECT TOP(1) [Lineage Key] FROM Integration.Lineage WHERE [Table Name] = N'Stock Holding' AND [Data Load Completed] IS NULL ORDER BY [Lineage Key] DESC); -- Find the dimension keys required UPDATE s SET s.[Stock Item Key] = COALESCE((SELECT TOP(1) si.[Stock Item Key] FROM Dimension.[Stock Item] AS si WHERE si.[WWI Stock Item ID] = s.[WWI Stock Item ID] ORDER BY si.[Valid To] DESC), 0) FROM Integration.StockHolding_Staging AS s; -- Remove all existing holdings TRUNCATE TABLE Fact.[Stock Holding]; -- Insert all current stock holdings INSERT Fact.[Stock Holding] ([Stock Item Key], [Quantity On Hand], [Bin Location], [Last Stocktake Quantity], [Last Cost Price], [Reorder Level], [Target Stock Level], [Lineage Key]) SELECT [Stock Item Key], [Quantity On Hand], [Bin Location], [Last Stocktake Quantity], [Last Cost Price], [Reorder Level], [Target Stock Level], @LineageKey FROM Integration.StockHolding_Staging; UPDATE Integration.Lineage SET [Data Load Completed] = SYSDATETIME(), [Was Successful] = 1 WHERE [Lineage Key] = @LineageKey; UPDATE Integration.[ETL Cutoff] SET [Cutoff Time] = (SELECT [Source System Cutoff Time] FROM Integration.Lineage WHERE [Lineage Key] = @LineageKey) FROM Integration.[ETL Cutoff] WHERE [Table Name] = N'Stock Holding'; COMMIT; RETURN 0; END; |
Exported: 2019-02-04 23:05, Last imported: 2018-08-10 09:59