CREATE DATABASE [ADVENTUREWORKSEMPLOYEE]
GO
USE [AdventureWorksEmployee]
GO
IF  EXISTS (SELECT * FROM sys.objects 
		WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Employee]') 
		AND type in (N'U'))
DROP TABLE [dbo].[tbl_Employee]
GO
USE [AdventureWorksEmployee]
GO
CREATE TABLE [dbo].[tbl_Employee](
 [EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
 [FirstName] [varchar](128) NULL,
 [LastName] [varchar](128) NULL,
 [Location] [varchar](128) NULL,
 [Phone] [varchar](128) NULL,
 CONSTRAINT [PK_tbl_Employee] PRIMARY KEY CLUSTERED 
(
 [EmployeeID] ASC
)WITH (PAD_INDEX  = OFF
		, STATISTICS_NORECOMPUTE  = OFF
		, IGNORE_DUP_KEY = OFF
		, ALLOW_ROW_LOCKS  = ON
		, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
					

Step 2 - Create Source Table (tbl_Employee_Staging

The next code example will create the staging table for our updated, new, and removed employees. In a real-world example this may be from a nightly extract of your HR data into this table.

USE [AdventureWorksEmployee]
GO
IF  EXISTS (SELECT * FROM sys.objects 
		WHERE object_id = OBJECT_ID(N'[dbo].[tbl_Employee_Staging]') 
		AND type in (N'U'))
DROP TABLE [dbo].[tbl_Employee_Staging]
GO
USE [AdventureWorksEmployee]
GO
CREATE TABLE [dbo].[tbl_Employee_Staging](
 [FirstName] [varchar](128) NULL,
 [LastName] [varchar](128) NULL,
 [Location] [varchar](128) NULL,
 [Phone] [varchar](128) NULL
) ON [PRIMARY]
GO
					

Step 3 - Insert Data into the Source and Target Table (tbl_Employee) & (tbl_Employee_Staging)

Next, we will enter some sample data into our tbl_Employee table and tbl_Employee_Staging table. The data in our tbl_Employee table is our existing data and the data in our tbl_Employee_Staging table is the data we want to add insert and delete. The row we want to insert into our tbl_Employee table is "Thomas","Liddle","DE","555-999-1234".

USE [AdventureWorksEmployee]
GO 
TRUNCATE TABLE [dbo].[tbl_Employee]
GO
TRUNCATE TABLE [dbo].[tbl_Employee_Staging]
GO
INSERT INTO [AdventureWorksEmployee].[dbo].[tbl_Employee]
            ([FirstName]
            ,[LastName]
            ,[Location]
            ,[Phone])
      VALUES
            ('John',
            'Doe',
            'RI',
            '555-123-4567')
 GO
 INSERT INTO [AdventureWorksEmployee].[dbo].[tbl_Employee]
            ([FirstName]
            ,[LastName]
            ,[Location]
            ,[Phone])
      VALUES
            ('Jane',
            'Doe',
            'WA',
            '555-444-7890')
 GO
 /*
 THIS IS OUR NEW ROW
 */
 INSERT INTO [AdventureWorksEmployee].[dbo].[tbl_Employee_Staging]
            ([FirstName]
            ,[LastName]
            ,[Location]
            ,[Phone])
      VALUES
            ('Thomas'
            ,'Liddle'
            ,'DE'
            ,'555-999-1234')
 /*
 END
 */
GO 
 
INSERT INTO [AdventureWorksEmployee].[dbo].[tbl_Employee_Staging]
            ([FirstName]
            ,[LastName]
            ,[Location]
            ,[Phone])
      VALUES
            ('John',
            'Doe',
            'RI',
            '555-123-4567')
 GO
 INSERT INTO [AdventureWorksEmployee].[dbo].[tbl_Employee_Staging]
            ([FirstName]
            ,[LastName]
            ,[Location]
            ,[Phone])
      VALUES
            ('Jane',
            'Doe',
            'WA',
            '555-444-7890')
 GO

Step 4 - Run the MERGE statement and insert data in tbl_Employee_Stage to tbl_Employee

The MERGE statement below will look for differences in the tbl_Employee table and the tbl_Employee_Staging table using the FirstName and LastName as a key. The #tbl_EmployeeChanges table allows you to view or audit the changes performed with the MERGE statement. This is possable with the OUTPUT statement.

USE AdventureWorksEmployee
GO
SELECT 'tbl_Employee_BEFORE',* FROM tbl_Employee 
GO

CREATE TABLE #tbl_EmployeeChanges
(
 [FirstNameDeleted] varchar(128)
 ,[LastNameDeleted] varchar(128)
 ,[LocationDeleted] varchar(128)
 ,[PhoneDeleted] varchar(128)
 ,[Action] varchar(128)
 ,[FirstNameInserted] varchar(128)
 ,[LastNameInserted] varchar(128)
 ,[LocationInserted] varchar(128)
 ,[PhoneInserted] varchar(128)
)
 
MERGE dbo.tbl_Employee AS target
    USING 
  (SELECT [FirstName],[LastName],[Location],[Phone] FROM dbo.tbl_Employee_Staging) AS source 
   ([FirstName],[LastName],[Location],[Phone])
    ON (
    target.[FirstName] = source.[FirstName]
    AND target.[LastName] = source.[LastName])
    WHEN MATCHED THEN 
        UPDATE 
   SET 
    [FirstName] = source.[FirstName]
    ,[LastName] = source.[LastName]
    ,[Location] = source.[Location]
    ,[Phone] = source.[Phone]
 WHEN NOT MATCHED THEN 
     INSERT ([FirstName],[LastName],[Location],[Phone])
     VALUES (source.[FirstName]
    ,source.[LastName]
    ,source.[Location]
    ,source.[Phone])
 WHEN NOT MATCHED BY SOURCE THEN 
  DELETE

     OUTPUT 
   deleted.[FirstName]
   ,deleted.[LastName]
   ,deleted.[Location]
   ,deleted.[Phone] 
   ,$action
   ,inserted.[FirstName]
   ,inserted.[LastName]
   ,inserted.[Location]
   ,inserted.[Phone] 
     INTO #tbl_EmployeeChanges;
GO
SELECT * FROM #tbl_EmployeeChanges
GO
drop table #tbl_EmployeeChanges
GO
SELECT 'tbl_Employee_AFTER',* FROM tbl_Employee