none
Compare two datatables RRS feed

  • Question

  • I want to compare two datatables and store the different rows in third datatable. What will be the efficient way to do that.

     

    Example

     

    Table1

     

    Empid EmpName Salary

    1 Sarika 100000

    2. Pooja 50000

    3. Neha 10000

     

    Table2

     

    1 Sarika 90000

    2. Pooja 50000

    3. Neha 10000

    4. Reena 10000

     

    Table 3

     

    1 Sarika 90000

    4. Reena 10000

     

     

    Thanks

    Sarika

     

    Tuesday, July 10, 2007 7:12 AM

Answers

  • If you need to compare all the columns, then you would need to make a loop through all the rows and columns. There is no built-in way to compare tables in this case.

    If you have primary keys in those datatables and only need to compare if rows with specific primary keys exist then you could create data relations between the datatables and loop through the rows to see if one of the tables has related records in another one.

    Tuesday, July 10, 2007 10:32 AM
    Moderator

All replies

  • If you need to compare all the columns, then you would need to make a loop through all the rows and columns. There is no built-in way to compare tables in this case.

    If you have primary keys in those datatables and only need to compare if rows with specific primary keys exist then you could create data relations between the datatables and loop through the rows to see if one of the tables has related records in another one.

    Tuesday, July 10, 2007 10:32 AM
    Moderator
  • hi sarika,

    have you got the solution about the problem? if no,Please have a look at the following steps.

    1.copy of first table including stucture and then to clear the new copied table values.
    2.set  primary key values on table 2.
    3.using table.rows.find method to compare.
    4.if it is found or not found may be depends on your requirement then import to the new copied table.

    Then met up our requirement...hope,you can understand that...if no please keep me posted..

    Thanks
    Venkatesan.K
    Friday, July 3, 2009 3:54 AM
  • I followed the above steps, here is the code snippet:

     

     

    Dim dt1 as dataTable

    Dim dt2 as dataTable

    Dim matchedDT as dataTable

    Dim unMatchedDT as dataTable

    matchedDT =  dt1.clone()

    unMatchedDT = dt1.clone()

     

     

    dt2.PrimaryKey = New DataColumn() {tableDT.Columns(0)), tableDT.Columns(1), tableDT.Columns(2), tableDT.Columns(3)}

     

     

     

            Dim MatchRow As DataRow

     

            For Each dr In dt1.Rows

                MatchRow = dt2.Rows.Find(dt2.PrimaryKey)

     

     

                If MatchRow Is Nothing Then 'If the record is not in Table 2  

                    unMatchedDT.ImportRow(dr)

     

                Else

     

                    matchedDT.ImportRow(MatchRow)

     

                End If

            Next

     

    But it still added  matched Row to unmatched dataTable, it seems it did not check the primary keys. Did I miss anything? Thank you!

     

    I followed the above steps, here is the code snippet:

    Wednesday, May 12, 2010 7:16 PM
  • Sarika,

    there is EXCEPT operator in SQL Server which does the comparison between two datatable. The same EXCEPT operator is available with LINQ query as well in .net -- i will suggest using this operator - it is quick and very less peace of code.

    more information can be found at http://ashishkhandelwal.arkutil.com/?p=454


    Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. My Blog: http://ashishkhandelwal.arkutil.com
    Wednesday, May 12, 2010 7:27 PM
  • Those 2 datatables are not physical tables in SQL server, they are datatable objects . How can i use Except operator, do you have examples? Thank you!

    Wednesday, May 12, 2010 8:22 PM
  • Sarika,

    The same question was posted in 2008, the answer is available there, check it out:

    http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/thread/23703a85-20c7-4759-806a-fabf4e9f5be6


    Please mark the response as answers if it solves your question or vote as helpful if you find it helpful. My Blog: http://ashishkhandelwal.arkutil.com
    Thursday, May 13, 2010 5:03 AM
  • I got an error when add relationship for data columns -- "Cannot have more than 32 columns"

    DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);  

    I have 54 columns in the datatable.

    Any ideas to fix this? Please Help! Thank you!

    Thursday, May 13, 2010 3:54 PM
  • Actually, I only need to compare the first 4 column values, but I do need to add different rows( whole row ,52 columns ) to the result datatable. How do I do that?

    Thursday, May 13, 2010 4:04 PM
  • Hi,

    I encounter the same need, that is comparing more than 32 columns. Did you find a workaround please?

    Friday, December 24, 2010 4:25 PM