USE [WineStoreDW]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE PROCEDURE [dbo].[usp_Load_dimCurrency]

--Implements an Upsert logic for Type 1 dimensions

--In this case the CurrencyCode is the field which is used to match the stage.Currency with dimCurrency

MERGE [dbo].[dimCurrency] AS [Target]

USING ( SELECT CurrencyCode,

CurrencyName

FROM [stage].[Currency]

) AS [Source]

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

WHEN MATCHED THEN

UPDATE SET

[Target].[CurrencyName] = [Source].[CurrencyName],

[Target].[UpdatedDate] = getdate()

WHEN NOT MATCHED THEN

INSERT (CurrencyCode, CurrencyName, InsertedDate, UpdatedDate )

VALUES ([Source].[CurrencyCode], [Source].[CurrencyName], getdate(), getdate() ); --INSERT STATEMENT