Merging of Data Table in SQL Server

Hi Guys,

This article will demonstrates how to merge two different datatables in sql into single table.Let’s assume there are 2 datatables and we have to merge both the datatables on the basis of Employee Id then,

  • Both the datatable should have a column with the the name say “EmpId”.
  • Values in columns “EmpId” of both the tables should be unique.

.Cs file code:

private DataTable DataTable1()
    {
        DataRow dataRow = null;
        DataTable dt1 = new DataTable();
        dt1.Columns.Add("EmpId");
        dt1.Columns.Add("EmpName");
        dataRow = dt1.NewRow();
        dataRow["EmpId"] = "EMP001";
        dataRow["EmpName"] = "Ajaj Kumar";
        dt1.Rows.Add(dataRow);
        dataRow = dt1.NewRow();
        dataRow["EmpId"] = "EMP002";
        dataRow["EmpName"] = "Sanjay Gupta";
        dt1.Rows.Add(dataRow);
        dt1.AcceptChanges();
        return dt1;
    }
private DataTable DataTable2()
    {
        DataRow dr = null;
        DataTable dt2 = new DataTable();
        dt2.Columns.Add("EmpId");
        dt2.Columns.Add("Salary");
        dr = dt2.NewRow();
        dr["EmpId"] = "EMP001";
        dr["Salary"] = "50000";
        dt2.Rows.Add(dr);
        dr = dt2.NewRow();
        dr["EmpId"] = "EMP002";
        dr["Salary"] = "45000";
        dt2.Rows.Add(dr);
        dt2.AcceptChanges();
        return dt2;
    }
protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dataTable1 = null, dataTable2 = null, dataTable3 = null;
            dataTable1 = DataTable1();
            dataTable2 = DataTable2();
            dataTable1.PrimaryKey = new DataColumn[] { dataTable1.Columns["EmpId"] };
            dataTable2.PrimaryKey = new DataColumn[] { dataTable2.Columns["EmpId"] };
            dataTable3 = dataTable1.Copy();
            dataTable3.Merge(dataTable2, false, MissingSchemaAction.Add);
            dataTable3.AcceptChanges();
            GridView1.DataSource = dataTable3;
            GridView1.DataBind();
        }
    }

Hope this will help you to understand the Merge() of DataTable.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s