/***********************************************************************
*
* Script to accompany the Frog-Blog post 
*           'Introduction to T-SQL Merge Basics'
*
* http://www.purplefrogsystems.com/blog/2011/12/introduction-to-t-sql-merge-basics
*
* Author: Alex Whittles
*         Purple Frog Systems
*         13th December 2011
*
* This information is provided "AS IS" with no warranties, confers no rights
*  and is used entirely at the users own risk.
*
***********************************************************************/

--Create the main Customer table
CREATE TABLE [dbo].[Customer](
	[CustomerID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [varchar](50) NOT NULL,
	[LastName] [varchar](50) NOT NULL,
	[Title] [varchar](10) NOT NULL,
	[DoB] [date] NOT NULL,
	[Email] [varchar](100) NOT NULL,
	[IsActive] bit NOT NULL,
	[DTInserted] [datetime] NOT NULL,
	[DTUpdated] [datetime] NOT NULL,
 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
(
	[CustomerID] 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
        ,IsActive
        ,DTInserted
        ,DTUpdated )
VALUES
        ( 'Dave',  'Robinson', 'Mr',   '1976-02-26', 'Dave@Robinson.com',    1, '2010-01-01 12:00', '2010-01-01 12:00')
       ,( 'Phil',  'Jones',    'Mr',   '1971-05-13', 'Phil@PhilJones.com',   1, '2010-01-01 12:00', '2010-01-01 12:00')
       ,( 'Sarah', 'Dawson',   'Mrs',  '1965-10-22', 'Sarah@TheDawsons.com', 1, '2010-01-01 12:00', '2010-01-01 12:00')
       ,( 'Andy',  'Powell',   'Dr',   '1948-03-01', 'Andy@emailme.com',     1, '2010-01-01 12:00', '2010-01-01 12:00')
       ,( 'Jane',  'Smith',    'Miss', '1984-07-19', 'Jane@emailme.com',     1, '2010-01-01 12:00', '2010-01-01 12:00')
    
    
--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



/*
MERGE Customer        AS [Target]
USING StagingCustomer AS [Source]
   ON Target.Email = Source.Email
WHEN MATCHED AND
    (
      Target.FirstName   <> Source.FirstName
      OR Target.LastName <> Source.LastName
      OR Target.Title    <> Source.Title
      OR Target.DoB      <> Source.DoB
    )
    THEN UPDATE SET
       FirstName = Source.FirstName
      ,LastName  = Source.LastName
      ,Title     = Source.Title
      ,DoB       = Source.DoB
      ,DTUpdated = GETDATE()
WHEN NOT MATCHED BY TARGET
    THEN INSERT (
        Email
       ,FirstName
       ,LastName
       ,Title
       ,DoB
       ,IsActive
       ,DTInserted
       ,DTUpdated
      ) VALUES (
        Source.Email
       ,Source.FirstName
       ,Source.LastName
       ,Source.Title
       ,Source.DoB
       ,1
       ,GETDATE()
       ,GETDATE()
      )
WHEN NOT MATCHED BY SOURCE
    THEN UPDATE SET
        IsActive = 0
      ;
*/