/***********************************************************************
*
* Script to accompany the Frog-Blog post
*           'Using Merge to load Data Warehouse Dimensions'
*
* http://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/
*
* Author: Alex Whittles
*         Purple Frog Systems
*         16th January 2012
*
* This information is provided "AS IS" with no warranties, confers no rights
*  and is used entirely at the users own risk.
*
***********************************************************************/
DROP TABLE dbo.Customer
DROP TABLE dbo.StagingCustomer
GO

--Create the main Customer table
CREATE TABLE [dbo].[Customer](
	[CustomerKey]   [int] IDENTITY(1,1) NOT NULL,
	[FirstName]     [varchar](50)  NULL,
	[LastName]      [varchar](50)  NULL,
	[Title]         [varchar](10)  NULL,
	[DoB]           [date]  NULL,
	[Email]         [varchar](100)  NULL,
	[LastUpdated]   DATETIME  NULL,
	[IsRowCurrent]  BIT  NULL,
	[ValidFrom]     DATETIME  NULL,
	[ValidTo]       DATETIME  NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
	[CustomerKey] ASC
))
--Create a staging table to hold new/updated customers
CREATE TABLE [dbo].[StagingCustomer](
	[FirstName]     [varchar](50) NOT NULL,
	[LastName]      [varchar](50) NOT NULL,
	[Title]         [varchar](10) NOT NULL,
	[DoB]           [date] NOT NULL,
	[Email]         [varchar](100) NOT NULL
)

--Populate Customer table with existing customers
INSERT INTO Customer
        ( FirstName
        ,LastName
        ,Title
        ,DoB
        ,Email
        ,LastUpdated
        ,IsRowCurrent
        ,ValidFrom
        ,ValidTo )
VALUES
        ( 'Dave',  'Robinson', 'Mr',   '1976-02-26', 'Dave@Robinson.com',    '2010-01-01 12:00', 1, '2010-01-01 12:00', '9999-12-31')
       ,( 'Phil',  'Jones',    'Mr',   '1971-05-13', 'Phil@PhilJones.com',   '2010-01-01 12:00', 1, '2010-01-01 12:00', '9999-12-31')
       ,( 'Sarah', 'Dawson',   'Mrs',  '1965-10-22', 'Sarah@TheDawsons.com', '2010-01-01 12:00', 1, '2010-01-01 12:00', '9999-12-31')
       ,( 'Andy',  'Powell',   'Dr',   '1948-03-01', 'Andy@emailme.com',     '2010-01-01 12:00', 1, '2010-01-01 12:00', '9999-12-31')
       ,( 'Jane',  'Smith',    'Miss', '1984-07-19', 'Jane@emailme.com',     '2010-01-01 12:00', 1, '2010-01-01 12:00', '9999-12-31')

--Populate the staging table with the new set of customers to be imported
INSERT INTO StagingCustomer
        ( FirstName
        ,LastName
        ,Title
        ,DoB
        ,Email)
VALUES
        ( 'Dave',  'Robinson', 'Mr',   '1976-02-26', 'Dave@Robinson.com')  --No change
       ,( 'Phil',  'Jones',    'Mr',   '1971-05-14', 'Phil@PhilJones.com') --Updated DoB
       ,( 'Jack',  'White',    'Mr',   '1979-01-05', 'Jack@JackWhite.com') --New customer
       ,( 'Jane',  'King',     'Mrs',  '1984-07-19', 'Jane@emailme.com')   --Updated Surname & Title
       ,( 'Mary',  'Scott',    'Mrs',  '1981-02-20', 'Mary@ScottMail.com') --New customer

SELECT * FROM Customer C
SELECT * FROM StagingCustomer SC

-- Mixed Merge - Type 1 & 2
INSERT INTO Customer
   ( FirstName
    ,LastName
    ,Title
    ,DoB
    ,Email
    ,LastUpdated
    ,IsRowCurrent
    ,ValidFrom
    ,ValidTo
   ) SELECT
     FirstName
    ,LastName
    ,Title
    ,DoB
    ,Email
    ,GETDATE()    --LastUpdated
    ,1            --IsRowCurrent
    ,GETDATE()    --ValidFrom
    ,'9999-12-31' --ValidTo
FROM (
  MERGE Customer        AS [Target]
  USING StagingCustomer AS [Source]
     ON Target.Email        = Source.Email
    AND Target.IsRowCurrent = 1
  WHEN MATCHED AND
      (
           Target.FirstName <> Source.FirstName
        OR Target.LastName  <> Source.LastName
        OR Target.Title     <> Source.Title
      )
      THEN UPDATE SET
         IsRowCurrent     = 0
        ,LastUpdated      = GETDATE()
        ,ValidTo          = GETDATE()
  WHEN NOT MATCHED BY TARGET
      THEN INSERT (
          FirstName
         ,LastName
         ,Title
         ,DoB
         ,Email
         ,LastUpdated
         ,IsRowCurrent
         ,ValidFrom
         ,ValidTo
        ) VALUES (
          Source.FirstName
         ,Source.LastName
         ,Source.Title
         ,Source.DoB
         ,Source.Email
         ,GETDATE()      --LastUpdated
         ,1              --IsRowCurrent
         ,GETDATE()      --ValidFrom
         ,'9999-12-31'   --ValidTo
        )
  WHEN NOT MATCHED BY SOURCE
      THEN UPDATE SET
          IsRowCurrent = 0
         ,LastUpdated  = GETDATE()
         ,ValidTo      = GETDATE()
  OUTPUT $action AS Action
        ,[Source].*
) AS MergeOutput
WHERE MergeOutput.Action = 'UPDATE'
  AND Email IS NOT NULL
;

--Now load the Type 1 changes
UPDATE C
    SET DoB         = SC.DoB
       ,LastUpdated = GETDATE()
FROM Customer C
   INNER JOIN StagingCustomer SC
        ON C.Email        =  SC.Email
     --AND C.IsRowCurrent =  1      --Optional
       AND C.DoB          <> SC.DoB