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:
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.
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.
|
|
|||||||||||||||||||||||||||||||||||
|