comparison of two tables data RSS

• • Reply
  

  Asif_vu

  Member

  2 Points

  6 Posts

  comparison of two tables data

  Jul 27, 2012 05:37 AM|LINK

  hello:

  i have two tables in a database. both have same columns.

  copy data from table1 to table2 only if the data is not already present in table1.

  it will only prevent copying the exact strings; if there are a difference of  a single alphbet or comma then it should allow to copy to other table.

  i want to put data from one table to other and also want comparison of the data if both are equal then not copy ; if both are not equal then copy to the other table.

• • Reply
  

  santosh.jagd...

  Star

  7625 Points

  1454 Posts

  Re: comparison of two tables data

  Jul 27, 2012 06:50 AM|LINK

  here is one sample code to compare 2 datatable

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

  {
  3            // Something to do with the Initialization of the FirstDataTable and SecondDataTable
  4
  5             DataTable dt;
  6             dt = getDifferentRecords(FirstDataTable, SecondDataTable);
  7
  8             if (dt.Rows.Count == 0)
  9                 MessageBox.Show("Equal");
  10             else
  11                 MessageBox.Show("Not Equal");
  12         }
  13
  14
  15
  16         #region Compare two DataTables and return a DataTable with DifferentRecords
  17         /// <summary>
  18         /// Compare two DataTables and return a DataTable with DifferentRecords
  19         /// </summary>
  20         /// <param name="FirstDataTable">FirstDataTable</param>
  21         /// <param name="SecondDataTable">SecondDataTable</param>
  22         /// <returns>DifferentRecords</returns>
  23         public DataTable getDifferentRecords(DataTable FirstDataTable, DataTable SecondDataTable)
  24         {
  25             //Create Empty Table
  26             DataTable ResultDataTable = new DataTable("ResultDataTable");
  27
  28             //use a Dataset to make use of a DataRelation object
  29             using (DataSet ds = new DataSet())
  30             {
  31                 //Add tables
  32                 ds.Tables.AddRange(new DataTable[] { FirstDataTable.Copy(), SecondDataTable.Copy() });
  33
  34                 //Get Columns for DataRelation
  35                 DataColumn[] firstColumns = new DataColumn[ds.Tables[0].Columns.Count];
  36                 for (int i = 0; i < firstColumns.Length; i++)
  37                 {
  38                     firstColumns[i] = ds.Tables[0].Columns[i];
  39                 }
  40
  41                 DataColumn[] secondColumns = new DataColumn[ds.Tables[1].Columns.Count];
  42                 for (int i = 0; i < secondColumns.Length; i++)
  43                 {
  44                     secondColumns[i] = ds.Tables[1].Columns[i];
  45                 }
  46
  47                 //Create DataRelation
  48                 DataRelation r1 = new DataRelation(string.Empty, firstColumns, secondColumns, false);
  49                 ds.Relations.Add(r1);
  50
  51                 DataRelation r2 = new DataRelation(string.Empty, secondColumns, firstColumns, false);
  52                 ds.Relations.Add(r2);
  53
  54                 //Create columns for return table
  55                 for (int i = 0; i < FirstDataTable.Columns.Count; i++)
  56                 {
  57                     ResultDataTable.Columns.Add(FirstDataTable.Columns[i].ColumnName, FirstDataTable.Columns[i].DataType);
  58                 }
  59
  60                 //If FirstDataTable Row not in SecondDataTable, Add to ResultDataTable.
  61                 ResultDataTable.BeginLoadData();
  62                 foreach (DataRow parentrow in ds.Tables[0].Rows)
  63                 {
  64                     DataRow[] childrows = parentrow.GetChildRows(r1);
  65                     if (childrows == null || childrows.Length == 0)
  66                         ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
  67                 }
  68
  69                 //If SecondDataTable Row not in FirstDataTable, Add to ResultDataTable.
  70                 foreach (DataRow parentrow in ds.Tables[1].Rows)
  71                 {
  72                     DataRow[] childrows = parentrow.GetChildRows(r2);
  73                     if (childrows == null || childrows.Length == 0)
  74                         ResultDataTable.LoadDataRow(parentrow.ItemArray, true);
  75                 }
  76                 ResultDataTable.EndLoadData();
  77             }
  78
  79             return ResultDataTable;
  80         }
  81         #endregion
  82

  Santosh J
  MCP
  
  • Edited by santosh.jagdale on Jul 27, 2012 06:50 AM
  • Marked as answer by decker dong - msft on Aug 03, 2012 01:48 AM
• • Reply
  

  Decker Dong ...

  All-Star

  118619 Points

  18779 Posts

  Re: comparison of two tables data

  Jul 29, 2012 01:54 AM|LINK

  Asif_vu

  i want to put data from one table to other and also want comparison of the data if both are equal then not copy ; if both are not equal then copy to the other table.

  Hi，

  1）Please first check whether the DataTables' rows' number is equalable to each other，Check the columns' number equals to each other……

  1.1）If the same, then loop each of the row and fetch each number and do comparation。

  1.2）If not the same, copy this。

  2）Else copy the whole table directly。

• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: comparison of two tables data

  Jul 29, 2012 10:36 AM|LINK

  Asif_vu

  copy data from table1 to table2 only if the data is not already present in table1.

  You mean, not already present in table2?

  INSERT INTO Table2 (column1, column2, column3)
  SELECT      Table1.column1
             ,Table1.column2
             ,Table1.column3
  FROM        Table1
  LEFT JOIN   Table2 ON  (Table1.column1 = Table2.column1)
                     AND (Table1.column2 = Table2.column2)
                     AND (Table1.column3 = Table2.column3)
  WHERE       Table2.column1 Is Null
  AND         Table2.column2 Is Null
  AND         Table2.column3 Is Null