Click here to Skip to main content
14,266,079 members
Rate this:
Please Sign up or sign in to vote.
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
Updated 8-Apr-12 23:44pm
v2
Comments
sonu_coder 9-Apr-12 5:38am
   
Sorry i didn't got your question :(
Rate this:
Please Sign up or sign in to vote.

Solution 1

If the value in DataTable1 is to be replaced with the matching value from DataTable2, then the following code can be used
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
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.
   
v2
Rate this:
Please Sign up or sign in to vote.

Solution 4

HI,

suppose consider
DataTable1 is dt1;

and DataTable 2 is dt2;
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.
   
v4
Rate this:
Please Sign up or sign in to vote.

Solution 2

if you are on SQL SERVER 2008 - better approach

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

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
   
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.
Rate this:
Please Sign up or sign in to vote.

Solution 3

select * from tabel2_ex
union all


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



use this query
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100