USE [WineStoreDW]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_Load_dimTerritory]

--Implements an Upsert logic for Type 1 dimensions

--In this case the TerritoryCode is the field which is used to match the stage.Territory with dimTerritory

MERGE [dbo].[dimTerritory] AS [Target]

USING ( SELECT [TerritoryCode],

[TerritoryName],

[TradeRegion],

[Continent]

FROM [stage].[Territory]

) AS [Source]

ON [Target].[TerritoryCode] = [Source].[TerritoryCode] --- specifies the condition

WHEN MATCHED THEN

UPDATE SET [Target].[TerritoryName] = [Source].[TerritoryName],

[Target].[TradeRegion] = [Source].[TradeRegion],

[Target].[Continent] = [Source].[Continent],

[Target].[UpdatedDate] = getdate()

WHEN NOT MATCHED THEN

INSERT ([TerritoryCode], [TerritoryName], [TradeRegion], [Continent], InsertedDate, UpdatedDate)

VALUES ([Source].[TerritoryCode], [Source].[TerritoryName], [Source].[TradeRegion], [Source].[Continent], getdate(), getdate() ); --INSERT STATEMENT