Click here to Skip to main content
15,885,365 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
can you please help me with this..


i have two table

table1

slno name year amount bank address company
1 John 2014 2000 ING oruf Google
2 Mik 2014 5000 ING tiejd Micro
3 Robin 2014 1000 INF trydh Jen

table2

slno name year amount bank address
1 John 2014 2000 ING oruf
2 MIk 2014 5000 ING tiejd
3 melon 2013 21551 SBI hhdh

i want to compare this two table from table1 to table2

If the fields matches like

[Matching fields ] after comparing from table1 to table2

slno name year amount bank address company
1 John 2014 2000 ING oruf Google
2 MIk 2014 5000 ING tiejd Micro


if file is matching then it will Insert those matching fileds from table1 to a new table[table3]
as an update


i am using Asp.net(Charp, C#) and sql server
Posted
Updated 18-Sep-14 22:14pm
v2

OK this is a solution of your current problem.
However, if you have such a table structure in your database with two tables this similar, you should reconsider your database design as it is a complete waste of space with two so similar tables.

My sample data
C#
DataTable dt1 = new DataTable();
dt1.Columns.Add("slno", typeof(int));
dt1.Columns.Add("name", typeof(string));
dt1.Columns.Add("year", typeof(double));
dt1.Columns.Add("amount", typeof(int));
dt1.Columns.Add("bank", typeof(string));
dt1.Columns.Add("address", typeof(string));
dt1.Columns.Add("company", typeof(string));

dt1.Rows.Add(1, "John", 2014, 2000, "ING", "oruf", "Google");
dt1.Rows.Add(2, "Mik", 2014, 5000, "ING", "tiejd", "Micro");
dt1.Rows.Add(3, "Robin", 2014, 1000, "INF", "trydh", "Jen");

DataTable dt2 = new DataTable();
dt2.Columns.Add("slno", typeof(int));
dt2.Columns.Add("name", typeof(string));
dt2.Columns.Add("year", typeof(int));
dt2.Columns.Add("amount", typeof(int));
dt2.Columns.Add("bank", typeof(string));
dt2.Columns.Add("address", typeof(string));

dt2.Rows.Add(1, "John", 2014, 2000, "ING", "oruf");
dt2.Rows.Add(2, "Mik", 2014, 5000, "ING", "tiejd");
dt2.Rows.Add(3, "melon", 2013, 21551, "SBI", "hhdh");

Assuming you have two tables called dt1 and dt2, you create a third table that has the same number of columns as dt1.
C#
DataTable dt3 = new DataTable();
dt3.Columns.Add("slno", typeof(int));
dt3.Columns.Add("name", typeof(string));
dt3.Columns.Add("year", typeof(int));
dt3.Columns.Add("amount", typeof(int));
dt3.Columns.Add("bank", typeof(string));
dt3.Columns.Add("address", typeof(string));
dt3.Columns.Add("company", typeof(string));



C#
// Get the row and column count from the smallest table
int noRows = (dt1.Rows.Count < dt2.Rows.Count) ? dt1.Rows.Count : dt2.Rows.Count;
int noCols = (dt1.Columns.Count < dt2.Columns.Count) ? dt1.Columns.Count : dt2.Columns.Count;

for (int i = 0; i < noRows; i++)
{
    DataRow dr1 = dt1.Rows[i];
    DataRow dr2 = dt2.Rows[i];
    bool equal = true;
    for (int j = 0; j < noCols; j++)
    {
         // If one column differs, then break and do not add this row
        if (!dr1[j].Equals(dr2[j]))
        {
            equal = false;
            break;
        }
    }
    if (equal)
    {
        // Add the contents from the row in Table1 into Table3
        dt3.LoadDataRow(dr1.ItemArray, true);
    }
}


You should probably read up on this topic. Even if it is not your design.
Database normalization[^]

3 Normal Forms Database Tutorial[^]

Database Normalization Basics[^]
 
Share this answer
 
v4
Comments
10923679 19-Sep-14 3:01am    
well those table is import . so i have to keep it like that .
i am saying it clearly to you

table1 is the fresh update.

table2 is the details about the employee...

and table3 (table where the fresh
update like employeeid,name, amount payment_mode etc. )
and table3 is the main table where i have to populate all the details which are available in table2 .


so i need to compare table1 with name, current_year, id and amount should be table1.amount<=table2.amount

whatever data i will get after comparing both(table1 and table2)
those will populate in table3 from table1 .
George Jonsson 19-Sep-14 3:20am    
That is what my solution does.
10923679 19-Sep-14 3:23am    
okay. thank you .. let me try with this.

so all this i will keep in a single

protected void Button11_Click(object sender, EventArgs e)
{
}
George Jonsson 19-Sep-14 3:33am    
Please don't post a lot of code in the comment section.

I showed you an example. You need to adapt it to your current code. I just assumed you have two data tables in your code, and I called them dt1 and dt2 in my example. Your names are probably different.
10923679 19-Sep-14 3:39am    
okay .
i have all the 3 tables in my database
Maybe this'll help you in comparing the tables:
Convert Set To Join SQL Parameter[^]
How To Compare The Rows Of 2 Tables[^]
Compare 2 Tables[^]
 
Share this answer
 
v2
Comments
George Jonsson 19-Sep-14 2:46am    
At least bother to put in a caption for the links.
NekoNao 19-Sep-14 3:09am    
Done.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900