Record merging consists of inserting the records of one table, referred to as the source, into another table, referred to as the target. When performing this operation, you will have the option to:

• Insert all records from the source to the target
• Update the records that meet a criterion
• Delete some records based on a condition

 The primary formula to merge two tables is:

MERGE Table1 AS Target
USING Table2 AS Source
ON Table1.CommonField = Table2.CommonField

WHEN MATCHED Matched Options
    THEN Match Operation(s)
WHEN NOT MATCHED BY TARGET
    THEN Not Matched By Target Operation(s)
WHEN NOT MATCHED BY SOURCE
    THEN Not Matched By Source Operation(s)

You start with the MERGE operator followed by the table to which the records will be added. You continue with the USING operator followed by the table from which the records will be retrieved. Here is an example:

MERGE Contractors AS Workers
USING Employees AS Teachers

You must specify the condition by which the records must correspond. To merge the records, the tables must have a common column. The columns don't have to have the same name but they should be of the same type (and size). To provide this information, type ON followed by the condition. Here is an example:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON Workers.ContractorCode = Teachers.EmployeeNumber

To make the statement easier to read, you can include it in parentheses. After specifying the tables and the records corresponding conditions, you must indicate what to do if/when a record from the source table meets a record from the target table. These conditions are set in the last part of the statement.

Consider the following tables:

using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Exercise : System.Windows.Forms.Form
{
    private Label        lblEmployees;
    private DataGridView dgvEmployees;
    private DataGridView dgvContractors;
    private Label        lblContractors;

    public Exercise()
    {
        InitializeComponent();
    }

    void InitializeComponent()
    {
        lblEmployees = new System.Windows.Forms.Label();
        lblEmployees.AutoSize = true;
        lblEmployees.Location = new System.Drawing.Point(12, 9);
        lblEmployees.Text = "Employees";

        dgvEmployees = new System.Windows.Forms.DataGridView();
        dgvEmployees.Anchor = AnchorStyles.Top | AnchorStyles.Bottom
                            | AnchorStyles.Left | AnchorStyles.Right;
        dgvEmployees.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
        dgvEmployees.Location = new System.Drawing.Point(12, 27);
        dgvEmployees.Size = new System.Drawing.Size(450, 124);

        lblContractors = new System.Windows.Forms.Label();
        lblContractors.Anchor = AnchorStyles.Bottom | AnchorStyles.Left;
        lblContractors.AutoSize = true;
        lblContractors.Location = new System.Drawing.Point(12, 156);
        lblContractors.Text = "Contractors";

        dgvContractors = new System.Windows.Forms.DataGridView();
        dgvContractors.Anchor = AnchorStyles.Bottom | AnchorStyles.Left | AnchorStyles.Right;
        dgvContractors.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize;
        dgvContractors.Location = new System.Drawing.Point(12, 174);
        dgvContractors.Size = new System.Drawing.Size(450, 124);

	ClientSize = new System.Drawing.Size(478, 308);

        Controls.Add(lblEmployees);
        Controls.Add(dgvEmployees);
        Controls.Add(lblContractors);
        Controls.Add(dgvContractors);

        StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
        Controls.Add(dgvEmployees);
        Controls.Add(dgvContractors);
        Text = "Exercise";
	Load += new EventHandler(ExerciseLoad);
    }

    private void ExerciseLoad(object sender, EventArgs e)
    {
        SqlCommand cmdExercise = null;
        SqlConnection cntExercise = null;
        SqlDataAdapter sdaExercise = null;

        using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
        {
            cmdExercise = new SqlCommand(
                    "CREATE TABLE Contractors(" +
                    "ContractorCode nchar(10), FName nvarchar(20)," +
                    "LName nvarchar(20), Wage decimal(6, 2));" +
                    "CREATE TABLE Employees(EmployeeNumber nchar(10)," +
                    "DateHired date, FirstName nvarchar(20), LastName nvarchar(20)," +
                    "HourlySalary money, EmploymentStatus nvarchar(20) null);" +
                    "INSERT INTO Contractors VALUES(N'350809', N'Mary', " +
                    "N'Shamberg', 14.20), (N'286606', N'Chryssa', N'Lurie', " +
                    "20.26), (N'415905', N'Ralph', N'Sunny', 15.55);" +
                    "INSERT INTO Employees VALUES(N'286018', N'20020426', " +
                    "N'Julie', N'Chance', 12.84, N'Full Time')," +
                    "(N'286606', N'19981008', N'Ayinda', N'Kaihibu', 9.52, " +
                    "N'Part Time'), (N'922620', N'20100815', N'Ann', N'Keans', " +
                    "20.52, N'Full Time'), (N'415905', N'20061222', N'Godwin', " +
                    "N'Harrison', 18.75, N'Full Time'), (N'682470', N'20080430', " +
                    "N'Timothy', N'Journ', 21.05, NULL);",
                            cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
        }

        using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
        {
            cmdExercise = new SqlCommand("SELECT * FROM Contractors;", cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
            sdaExercise = new SqlDataAdapter(cmdExercise);
            DataSet dsExercise = new DataSet("ContractorsSet");

            sdaExercise.Fill(dsExercise);
            dgvContractors.DataSource = dsExercise.Tables[0];
        }

        using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
        {
            cmdExercise = new SqlCommand("SELECT * FROM Employees;", cntExercise);

            cntExercise.Open();
            cmdExercise.ExecuteNonQuery();
            sdaExercise = new SqlDataAdapter(cmdExercise);
            DataSet dsExercise = new DataSet("EmployeesSet");

            sdaExercise.Fill(dsExercise);
            dgvEmployees.DataSource = dsExercise.Tables[0];
        }
    }

    [STAThread]
    public static int Main()
    {
        System.Windows.Forms.Application.Run(new Exercise());
        return 0;
    }
}

This would produce:

When merging records, the first conditional operation is to decide what to do if two records match. To start, you would add a WHEN MATCHED statement:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED

If two records of the tables match, one thing you can do is to delete the matching record. To do this, after the WHEN MATCHED expression, add a THEN DELETE statement. Here is an example:

private void Exercise_Load(object sender, EventArgs e)
{
    SqlCommand cmdExercise = null;
    SqlConnection cntExercise = null;
    SqlDataAdapter sdaExercise = null;

    using (cntExercise = new SqlConnection("Data Source=(local);" +
                                           "Database='Exercise1';" +
                                           "Integrated Security=yes;"))
    {
        cmdExercise = new SqlCommand(
            "MERGE Contractors AS Workers " +
            "USING Employees AS Teachers " +
            "ON (Workers.ContractorCode = Teachers.EmployeeNumber) " +
            "WHEN MATCHED " +
            "THEN DELETE;",
                    cntExercise);

        cntExercise.Open();
        cmdExercise.ExecuteNonQuery();

        MessageBox.Show("The merge has been performed.",
                        "Exercise",
                        MessageBoxButtons.OK, MessageBoxIcon.Information);
    }

    using (cntExercise = new SqlConnection("Data Source=(local);" +
                                       "Database='Exercise1';" +
                                       "Integrated Security=yes;"))
    {
        cmdExercise = new SqlCommand("SELECT * FROM Contractors;", cntExercise);

        cntExercise.Open();
        cmdExercise.ExecuteNonQuery();
        sdaExercise = new SqlDataAdapter(cmdExercise);
        DataSet dsExercise = new DataSet("ContractorsSet");

        sdaExercise.Fill(dsExercise);
        dgvContractors.DataSource = dsExercise.Tables[0];
    }

    using (cntExercise = new SqlConnection("Data Source=(local);" +
                                       "Database='Exercise1';" +
                                       "Integrated Security=yes;"))
    {
        cmdExercise = new SqlCommand("SELECT * FROM Employees;", cntExercise);

        cntExercise.Open();
        cmdExercise.ExecuteNonQuery();
        sdaExercise = new SqlDataAdapter(cmdExercise);
        DataSet dsExercise = new DataSet("EmployeesSet");

        sdaExercise.Fill(dsExercise);
        dgvEmployees.DataSource = dsExercise.Tables[0];
    }
}

Imagine that you want to merge the records of both tables by inserting the employees in the Contractors table. Instead of deleting matching records, another option is to update the matched records. Imagine you have two employees who have the same employee number as two contractors. In this case, a simple solution we will use is to precede those employee numbers by 00. We can write the same statement as follows:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET ContractorCode = N'00' + ContractorCode

The other operation is to specify what to do if the records don't match: Simple, we will simply merge them (the employees) with the others (the contractors). This can be done as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace MergingRecords
{
    public partial class Exercise : Form
    {
        public Exercise()
        {
            InitializeComponent();
        }

        private void btnCreateTables_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise = new SqlConnection("Data Source=(local);" +
                                                   "Database='Exercise1';" +
                                                   "Integrated Security=yes;"))
            {
                SqlCommand cmdExercise = new SqlCommand(
                    "DROP TABLE Contractors; " +
                    "DROP TABLE Employees; " +
                    "CREATE TABLE Contractors(" +
                    "ContractorCode nchar(10), FName nvarchar(20)," +
                    "LName nvarchar(20), Wage decimal(6, 2));" +
                    "CREATE TABLE Employees(EmployeeNumber nchar(10)," +
                    "DateHired date, FirstName nvarchar(20), LastName nvarchar(20)," +
                    "HourlySalary money, EmploymentStatus nvarchar(20) null);" +
                    "INSERT INTO Contractors VALUES(N'350809', N'Mary', " +
                    "N'Shamberg', 14.20), (N'286606', N'Chryssa', N'Lurie', " +
                    "20.26), (N'415905', N'Ralph', N'Sunny', 15.55);" +
                    "INSERT INTO Employees VALUES(N'286018', N'20020426', " +
                    "N'Julie', N'Chance', 12.84, N'Full Time')," +
                    "(N'286606', N'19981008', N'Ayinda', N'Kaihibu', 9.52, " +
                    "N'Part Time'), (N'922620', N'20100815', N'Ann', N'Keans', " +
                    "20.52, N'Full Time'), (N'415905', N'20061222', N'Godwin', " +
                    "N'Harrison', 18.75, N'Full Time'), (N'682470', N'20080430', " +
                    "N'Timothy', N'Journ', 21.05, NULL);",
                    cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();

                MessageBox.Show("The Contractors and Employees tables have been created.",
                                "Exercise",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        private void btnSelectRecords_Click(object sender, EventArgs e)
        {
            SqlCommand cmdExercise = null;
            SqlConnection cntExercise = null;
            SqlDataAdapter sdaExercise = null;

            using (cntExercise = new SqlConnection("Data Source=(local);" +
                                                       "Database='Exercise1';" +
                                                       "Integrated Security=yes;"))
            {
                cmdExercise = new SqlCommand("SELECT * FROM Contractors;", cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();
                sdaExercise = new SqlDataAdapter(cmdExercise);
                DataSet dsExercise = new DataSet("ContractorsSet");

                sdaExercise.Fill(dsExercise);
                dgvContractors.DataSource = dsExercise.Tables[0];
            }

            using (cntExercise = new SqlConnection("Data Source=(local);" +
                                               "Database='Exercise1';" +
                                               "Integrated Security=yes;"))
            {
                cmdExercise = new SqlCommand("SELECT * FROM Employees;", cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();
                sdaExercise = new SqlDataAdapter(cmdExercise);
                DataSet dsExercise = new DataSet("EmployeesSet");

                sdaExercise.Fill(dsExercise);
                dgvEmployees.DataSource = dsExercise.Tables[0];
            }
        }

        private void btnMergeRecords_Click(object sender, EventArgs e)
        {
            using (SqlConnection cntExercise = new SqlConnection("Data Source=(local);" +
                                                   "Database='Exercise1';" +
                                                   "Integrated Security=yes;"))
            {
                SqlCommand cmdExercise = new SqlCommand(
                    "MERGE Contractors AS Workers " +
                    "USING Employees AS Teachers " +
                    "ON (Workers.ContractorCode = Teachers.EmployeeNumber) " +
                    "WHEN MATCHED " +
                    "    THEN UPDATE SET ContractorCode = N'00' + ContractorCode " +
                    "WHEN NOT MATCHED " +
                    "    THEN INSERT(ContractorCode, FName, LName, Wage) " +
                    "    VALUES(EmployeeNumber, FirstName, LastName, HourlySalary);",
                            cntExercise);

                cntExercise.Open();
                cmdExercise.ExecuteNonQuery();

                MessageBox.Show("The merge has been performed.",
                                "Exercise",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }
    }
}

We can then see the contents of both tables again to see what records each contains now:

Of course, in the same way, you can merge the records of any table to those of the other table, as long as you follow the rules. For example, based on the above tables, if you want to merge the contractors with the employees, you can specify the Employees table as the target, the Contractors table as source, and their numbers as the common column to match. Then, when the numbers match, you can specify what to do. For us, once more we can simply ask the database engine to start the number with 00. For the records that don't match, we can simply add the records from the source to the targe. Here is an example:

MERGE Employees AS Teachers
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO

When you write WHEN NOT MATCHED, it is assumed that you want the cases where a record of the target matches a record of the source. As a result, you can also write the expression as WHEN NOT MATCHED BY TARGET:

MERGE Employees AS Teachers
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO

To specify what to do if a record of the source matches one from the target, add a WHEN NOT MATCHED BY SOURCE statement. In this case, you must use either a DELETE or an UPDATE statment.

• Delete the whole code in the Query window and replace it with:
  

  USE CarDealer;
  GO
  
  MERGE UsedVehicles AS Target
  USING NewCars AS Source
  ON (Target.VehicleNumber = Source.CarCode)
  WHEN NOT MATCHED BY Target
      THEN INSERT(VehicleNumber, YearManufactured,
  	        Make, Model, OriginalPrice)
  	 VALUES(CarCode, CarYear, Make, Model, Price)
  WHEN MATCHED
      THEN UPDATE SET Target.YearManufactured = Source.CarYear,
  		    Target.Make = Source.Make,
  		    Target.Model = Source.Model,
  		    Target.OriginalPrice = Source.Price
  GO

If you do a merge using the above formula, after the merge has been performed, you would not know the result9s) unless you run a new query on the target table. Fortunately, you can ask the database engine to immediately display a summary of what happened. To do this, after the last THEN statement, create an OUTPUT expression. The formula to follow is:

MERGE Table1 AS Target
USING Table2 AS Source
ON Table1.CommonField = Table2.CommonField
WHEN MATCHED Matched Options
    THEN Match Operation(s)
WHEN NOT MATCHED BY TARGET Not Matched By Target Options
	THEN Not Matched By Target Operation(s)
WHEN NOT MATCHED BY SOURCE Not Matched By Source Options
	THEN Not Matched By Source Operation(s)
OUTPUT $action, DELETED | INSERTED | from_table_name.*

To get a summary of the merging operation(s), if you are performing only one type of operation, type OUTPUT, followed by either inserted.* or deleted.*. If you are performing different types of operations, type OUTPUT, followed by $action, followed by either inserted.* or deleted.* or both.

1. On the form, click the top data grid view
2. In the Properties window, click (Name) and type dgvCars
3. On the form, click the bottom data grid view and press Delete
    
   
4. Change the code of the Click event as follows:
   

   private void btnPresentCars_Click(object sender, EventArgs e)
   {
       using (SqlConnection conCarDealer =
   	new SqlConnection("Data Source=(local);" +
                             "Database='CarDealer1';" +
                             "Integrated Security='SSPI';"))
       {
           SqlCommand cmdCarDealer =
               new SqlCommand("MERGE UsedVehicles AS Target " +
                              "USING NewCars AS Source " +
                              "ON (Target.VehicleNumber = Source.CarCode)" +
                              "WHEN NOT MATCHED BY Target " +
                              "    THEN INSERT(VehicleNumber, YearManufactured," +
                              "    	    Make, Model, OriginalPrice)" +
                              "         VALUES(CarCode, CarYear, Make, Model, Price)" +
                              "WHEN MATCHED " +
                              "    THEN UPDATE SET Target.YearManufactured = Source.CarYear," +
                              "        		Target.Make = Source.Make," +
                              "        		Target.Model = Source.Model," +
                              "        		Target.OriginalPrice = Source.Price " +
                              "OUTPUT $action, inserted.*, deleted.*;",
                              conCarDealer);
           SqlDataAdapter sdaCarDealer =
               new SqlDataAdapter(cmdCarDealer);
           BindingSource bsCarDealer = new BindingSource();
   
           DataSet dsCarDealer = new DataSet("CarDealerSet");
           sdaCarDealer.Fill(dsCarDealer);
   
           conCarDealer.Open();
           bsCarDealer.DataSource = dsCarDealer.Tables[0];
           dgvCars.DataSource = bsCarDealer;
       }
   }

5. Press F5 to execute
6. Click Present Cars
   

7. Close the form and return to your programming environment
8. Change the design of the form as follows:
    

   Control Text Name Anchor Button Present Cars btnPresentCars   Label Cars     DataGridView   dgvCars Top, Left, Right Label Used Vehicles   Bottom, Left, Right DataGridView   dgvUsedVehicles Top, Bottom, Left, Right

9. Double-click the Present Cars button
10. To see the results on the target table, change the code as follows:
    

    private void btnPresentCars_Click(object sender, EventArgs e)
    {
        using (SqlConnection conCarDealer =
        new SqlConnection("Data Source=(local);" +
                              "Database='CarDealer1';" +
                              "Integrated Security='SSPI';"))
        {
            SqlCommand cmdCarDealer =
                new SqlCommand("MERGE UsedVehicles AS Target " +
                               "USING NewCars AS Source " +
                               "ON (Target.VehicleNumber = Source.CarCode)" +
                               "WHEN NOT MATCHED BY Target " +
                               "    THEN INSERT(VehicleNumber, YearManufactured," +
                               "    	    Make, Model, OriginalPrice)" +
                               "         VALUES(CarCode, CarYear, Make, Model, Price)" +
                               "WHEN MATCHED " +
                               "    THEN UPDATE SET Target.YearManufactured = Source.CarYear," +
                               "        		Target.Make = Source.Make," +
                               "        		Target.Model = Source.Model," +
                               "        		Target.OriginalPrice = Source.Price " +
                               "OUTPUT $action, inserted.*, deleted.*;",
                               conCarDealer);
            SqlDataAdapter sdaCarDealer =
                new SqlDataAdapter(cmdCarDealer);
            BindingSource bsCarDealer = new BindingSource();
    
            DataSet dsCarDealer = new DataSet("CarDealerSet");
            sdaCarDealer.Fill(dsCarDealer);
    
            conCarDealer.Open();
            bsCarDealer.DataSource = dsCarDealer.Tables[0];
            dgvCars.DataSource = bsCarDealer;
        }
    
        using (SqlConnection cnnCarDealer =
    	new SqlConnection("Data Source=(local);" +
                  		  "Database='CarDealer1';" +
                  		  "Integrated Security='SSPI';"))
        {
            SqlCommand cmdCarDealer =
                new SqlCommand("SELECT ALL * FROM UsedVehicles;",
                               cnnCarDealer);
            SqlDataAdapter sdaCarDealer =
                new SqlDataAdapter(cmdCarDealer);
            BindingSource bsCarDealer = new BindingSource();
    
            DataSet dsCarDealer = new DataSet("CarDealerSet");
            sdaCarDealer.Fill(dsCarDealer);
    
            cnnCarDealer.Open();
            bsCarDealer.DataSource = dsCarDealer.Tables[0];
            dgvUsedVehicles.DataSource = bsCarDealer;
        }
    }

11. Press F5 to execute
12. Click the Present Cars button
    

13. Close the form and return to your programming environment