Merging two data tables

Question

0.00/5 (No votes)

See more:

Hi Everybody,

I have two datatables like, I want to merge the below tables Based on "Id"

condition.

DataTable1
-----------

Id          ProductID       Value
---         ---------       -----
10C            1             10
10C            2             20
10C            3             30
20C            1             10
20C            2             20
20C            3             30

DataTable2
----------

Id          ProductID       Value
---         ---------       -----
10C            1             100
10C            2             200
10C            3             300

For the above tables Id "10C" present in both tables. based on that i want to replace the "Values of DataTable1" with the values of DataTable2.

Expected Out :

Id          ProductID       Value
---         ---------       -----
10C            1             100
10C            2             200
10C            3             300
20C            1             10
20C            2             20
20C            3             30

Thanks in Advance.

[edit]Formatting added - OriginalGriff[/edit]

Posted 8-Apr-12 23:37pm

naveenvenkanna

Updated 8-Apr-12 23:44pm

OriginalGriff

v2

Add a Solution

Comments

sonu_coder 9-Apr-12 5:38am

Sorry i didn't got your question :(

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

VJ Reddy · Answer 1 · 2012-04-09T00:21:00

If the value in DataTable1 is to be replaced with the matching value from DataTable2, then the following code can be used

C#

foreach(DataRow row in DataTable1.Rows){
    DataRow[] rows2 = DataTable2.Select(string.Format("Id='{0}' and ProductId = '{1}'",row["Id"],
                    row["ProductId"]));
    if (rows2.Length > 0) {
        row["Value"]=rows2[0]["Value"];
    }
}

For a quick test, paste the following code

C#

void Main()
{
    DataTable DataTable1 = new DataTable("DataTable1");
    DataTable1.Columns.Add("Id",typeof(string));
    DataTable1.Columns.Add("ProductId",typeof(string));
    DataTable1.Columns.Add("Value",typeof(string));
    DataTable1.Rows.Add("10C","1","10");
    DataTable1.Rows.Add("10C","2","20");
    DataTable1.Rows.Add("10C","3","30");
    DataTable1.Rows.Add("20C","1","10");
    DataTable1.Rows.Add("20C","2","20");
    DataTable1.Rows.Add("20C","3","30");
    DataTable DataTable2 = new DataTable("DataTable2");
    DataTable2.Columns.Add("Id",typeof(string));
    DataTable2.Columns.Add("ProductId",typeof(string));
    DataTable2.Columns.Add("Value",typeof(string));
    DataTable2.Rows.Add("10C","1","100");
    DataTable2.Rows.Add("10C","2","200");
    DataTable2.Rows.Add("10C","3","300");
    
    foreach(DataRow row in DataTable1.Rows){
    	DataRow[] rows = DataTable2.Select(string.Format("Id='{0}' 
            and ProductId = '{1}'",row["Id"],row["ProductId"]));
    	if (rows.Length > 0) {
    		row["Value"]=rows[0]["Value"];
    	}
    	
    }
    DataTable1.Dump();
}

in LINQPad, which can be downloaded from here http://www.linqpad.net/[^], select C# Program in Language combo box and run the program.

Anil Avhad [IND] · Answer 2 · 2012-04-09T01:02:00

HI,

suppose consider
DataTable1 is dt1;

and DataTable 2 is dt2;

C#

if(dt1!=null && dt1.Rows.count>0
{
    if(dt2!=null && dt2.Rows.count>0)
  {
 
       dt1.DefaultView.RowFilter = " id<>'10C'";
            if (dt1.DefaultView.ToTable().Rows.Count > 0)
            {
                dt2.Merge(dt1.DefaultView.ToTable());
            }  
 }
}

it Means dt2 Will return both Tables Data.

Bala Selvanayagam · Answer 3 · 2012-04-09T00:33:00

Solution 2

if you are on SQL SERVER 2008 - better approach

SQL

merge DataTable1 as dt1
using DataTable2 as dt2
on dt1.ID=dt2.id and dt1.ProductID=dt2.productID
when matched then update set dt1.value=dt2.value;

Any SQL SERVER version

SQL

update DataTable1
set value= isnull((select dt2.value
                from DataTable2 dt2
                where (DataTable1.ID=dt2.ID and DataTable1.productID=dt2.ProductID) and DataTable1.value <> dt2.value),DataTable1.value)

Hope this helps

Posted 9-Apr-12 0:33am

Bala Selvanayagam

Updated 9-Apr-12 0:47am

v2

Comments

Arul R Ece 9-Apr-12 6:51am

Hi bala....
when i use the first query only 3 rows were affected.

Bala Selvanayagam 9-Apr-12 7:13am

I am not sure whether the OP wants to do this at database level / in the presentation layer, my solution is based on the assumtpion at database level

The OP wants to merge the DataTable2 into Datatable1 and there are only three rows are to be merged in the first case.

Arul R Ece · Answer 4 · 2012-04-09T00:36:00

Solution 3

SQL

select * from tabel2_ex
union all


select * from tabel1_ex where id != '10c'

use this query

Posted 9-Apr-12 0:36am

Arul R Ece

Merging two data tables

4 solutions

Solution 1

Solution 4

Solution 2

Solution 3

Add your solution here

Preview 0