Compare two tables and insert the non matching records into another table using mysql with c#

Question

I have two table A and B with same column and structure i want to compare both tables and insert non matching records from table B into another table. I am using C# and MYSql.

            cnn.Open();

            string Csql1 = "SELECT FILE_NAME, passport_number, applicant_name, applicant_dob, applicant_gender,issue_date, visa_number, fh_name, application_date, rowid FROM Data_Before_QC ORDER BY old_name ASC LIMIT 1";
            da1 = new MySqlDataAdapter(Csql1, cnn);
            da1.Fill(dt1);
            //string[] arr1 = new string[dt1.Rows.Count];

            string Csql2 = "SELECT FILE_NAME, passport_number, applicant_name, applicant_dob,applicant_gender,issue_date,visa_number, fh_name, application_date, rowid FROM Data_Afetr_QC ORDER BY old_name ASC LIMIT 1";
            da2 = new MySqlDataAdapter(Csql2, cnn);
            da2.Fill(dt2);


            foreach (DataRow row1 in dt1.Rows)
            {
                foreach (DataRow row2 in dt2.Rows)
                {

                }
            }

Why not do it on the database in a single statement (insert into tableDest... select from tableA where not exists (select from tableB...)? — Richard, Aug 20 '15 at 14:24
@Richard probably because this will not update the values in the columns. Also the question is structure really bad. — mybirthname, Aug 20 '15 at 14:25
Try something with linq, like this: http://stackoverflow.com/questions/12883790/comparing-two-datatables-through-linq — andre, Aug 20 '15 at 14:29

mybirthname · Answer 1 · 2015-08-20T14:52:20.827

You need something like that:

      string[] fields = new string[]{

                                "passport_number",
                                "applicant_name",
                                //and so on you should write all of your columns which you wanna update.
                            };

      foreach (DataRow sourceRow in dt1.Rows)
      {
           var searchResult = from dRow in dt2.AsEnumerable()
                              where dRow["FILE_NAME"].Equals(sourceRow["FILE_NAME"])
           select dRow;

           if(!searchResult.Any())
               continue;



           foreach(string columnName in fields)
           {
               searchResult[columnName] = sourceRow[columnName];
           } 

      }

Be aware this will not update you the missing records. For that you need another insert @Richard write you in the comments how. For this code to work you need to add using System.Linq;. Also I suppose you are using FILE_NAME for coparing the records.

Also there is tools for this kind of work, if you think to do this seriously. If you can't afford a tool and you want to update a little number of tables you should take look in Microsoft SMO. About the SMO, be aware that the performance is not good !

P.S Next time put more effort in the question in the other case probably nobody will answer you ! It is not that hard to think what to do when you loop your tables.

The code is build on the fly so it is possible to have mistakes !

thanks for all your kind reply i agree database is not well designed by previous developer. suppost A table have fieldname name which contain value "A" and B table have same fieldname name but it is null so fieldname from table B should insert in table C. — user1528069, Aug 27 '15 at 11:00
@user1528069 If the answer help you make it mark it as correct. — mybirthname, Aug 27 '15 at 11:02

score 0 · Answer 2 · answered Aug 20 '15 at 14:56

Better edit the SQL query like this

SELECT * FROM(
    SELECT FILE_NAME, passport_number, applicant_name, applicant_dob, applicant_gender,issue_date, visa_number, fh_name, application_date, rowid FROM Data_Before_QC ORDER BY old_name ASC LIMIT 1
    EXCEPT
    SELECT FILE_NAME, passport_number, applicant_name, applicant_dob,applicant_gender,issue_date,visa_number, fh_name, application_date, rowid FROM Data_Afetr_QC ORDER BY old_name ASC LIMIT 1) tmp

Note : Code is not tested

thanks for all your reply but both table contains more than 40000 records and i am using MySQL as backend. — user1528069, Aug 27 '15 at 06:32

Compare two tables and insert the non matching records into another table using mysql with c#

2 Answers2