/*********************************************************************** * * 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 ; */