Integration.MigrateStagedPurchaseData
Documentation | WideWorldImportersDW |
Schema | Integration |
Name | MigrateStagedPurchaseData |
Script
CREATE PROCEDURE Integration.MigrateStagedPurchaseData 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'Purchase' AND [Data Load Completed] IS NULL ORDER BY [Lineage Key] DESC); -- Find the dimension keys required UPDATE p SET p.[Supplier Key] = COALESCE((SELECT TOP(1) s.[Supplier Key] FROM Dimension.Supplier AS s WHERE s.[WWI Supplier ID] = p.[WWI Supplier ID] AND p.[Last Modified When] > s.[Valid From] AND p.[Last Modified When] <= s.[Valid To] ORDER BY s.[Valid From]), 0), p.[Stock Item Key] = COALESCE((SELECT TOP(1) si.[Stock Item Key] FROM Dimension.[Stock Item] AS si WHERE si.[WWI Stock Item ID] = p.[WWI Stock Item ID] AND p.[Last Modified When] > si.[Valid From] AND p.[Last Modified When] <= si.[Valid To] ORDER BY si.[Valid From]), 0) FROM Integration.Purchase_Staging AS p; -- Remove any existing entries for any of these purchase orders DELETE p FROM Fact.Purchase AS p WHERE p.[WWI Purchase Order ID] IN (SELECT [WWI Purchase Order ID] FROM Integration.Purchase_Staging); -- Insert all current details for these purchase orders INSERT Fact.Purchase ([Date Key], [Supplier Key], [Stock Item Key], [WWI Purchase Order ID], [Ordered Outers], [Ordered Quantity], [Received Outers], Package, [Is Order Finalized], [Lineage Key]) SELECT [Date Key], [Supplier Key], [Stock Item Key], [WWI Purchase Order ID], [Ordered Outers], [Ordered Quantity], [Received Outers], Package, [Is Order Finalized], @LineageKey FROM Integration.Purchase_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'Purchase'; COMMIT; RETURN 0; END; |
Exported: 2019-02-04 23:05, Last imported: 2018-08-10 09:59