Integration.MigrateStagedStockItemData
Documentation | WideWorldImportersDW |
Schema | Integration |
Name | MigrateStagedStockItemData |
Script
CREATE PROCEDURE Integration.MigrateStagedStockItemData WITH EXECUTE AS OWNER AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; DECLARE @EndOfTime datetime2(7) = '99991231 23:59:59.9999999'; BEGIN TRAN; DECLARE @LineageKey int = (SELECT TOP(1) [Lineage Key] FROM Integration.Lineage WHERE [Table Name] = N'Stock Item' AND [Data Load Completed] IS NULL ORDER BY [Lineage Key] DESC); WITH RowsToCloseOff AS ( SELECT s.[WWI Stock Item ID], MIN(s.[Valid From]) AS [Valid From] FROM Integration.StockItem_Staging AS s GROUP BY s.[WWI Stock Item ID] ) UPDATE s SET s.[Valid To] = rtco.[Valid From] FROM Dimension.[Stock Item] AS s INNER JOIN RowsToCloseOff AS rtco ON s.[WWI Stock Item ID] = rtco.[WWI Stock Item ID] WHERE s.[Valid To] = @EndOfTime; INSERT Dimension.[Stock Item] ([WWI Stock Item ID], [Stock Item], Color, [Selling Package], [Buying Package], Brand, Size, [Lead Time Days], [Quantity Per Outer], [Is Chiller Stock], Barcode, [Tax Rate], [Unit Price], [Recommended Retail Price], [Typical Weight Per Unit], Photo, [Valid From], [Valid To], [Lineage Key]) SELECT [WWI Stock Item ID], [Stock Item], Color, [Selling Package], [Buying Package], Brand, Size, [Lead Time Days], [Quantity Per Outer], [Is Chiller Stock], Barcode, [Tax Rate], [Unit Price], [Recommended Retail Price], [Typical Weight Per Unit], Photo, [Valid From], [Valid To], @LineageKey FROM Integration.StockItem_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 Item'; COMMIT; RETURN 0; END; |
Exported: 2019-02-04 23:05, Last imported: 2018-08-10 09:59