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.
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。
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
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.
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 82MCP
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: comparison of two tables data
Jul 29, 2012 01:54 AM|LINK
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。
hans_v
All-Star
35986 Points
6550 Posts
Re: comparison of two tables data
Jul 29, 2012 10:36 AM|LINK
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