Get the Matching rows When DT1 compared to DT2

[Dhruvi_Arumugam]

Hi Everyone,

Need urgent help on one scenario.

I have 2 Datatables say Dt1 and Dt2.

DT 1 has say 193000 rows which are unique. I want to compare this DT1 data with DT2 data and store the matching results in a 3rd table. (DT2 is a huge datatable)

Note - DT2 will have duplicate records of the DT1 records, so I want them as well I want the total count without eliminating any duplicates or data
.

Please help !

Thanks

[Dhruvi_Arumugam]

Hi @Yoichi @ppr and others as well can you please help!!!

[Yoichi]

Hi,

Do you mean you need to leave rows of dt2? If so, can you try the following expression?

dt3 = dt2.AsEnumerable.Where(Function(r2) dt1.AsEnumerable.Any(Function(r1) DataRowComparer.Default.Equals(r1,r2))).CopyToDataTable

Regards,

[Dhruvi_Arumugam]

Hi @Yoichi,

Thanks for the response, whereas the where clause is generally inefficient and my data size is huge this cam may be degrade the processing.

Can you also please suggest with some Join, list other approaches also that can be helpful.

Thanks in advance!

[Yoichi]

Hi,

I don’t think Where is very slow. However double loop might be inefficient.
The following post is to leave mismatched rows using sort and single loop. This might help you.

Not getting expected output from Except function

Hi, If your data doesn’t contains tab (for example), can you try the following? [image] dt1 = dt1.AsEnumerable.OrderBy(Function(r) String.Join(chr(9),r.ItemArray)).CopyToDataTable dt2 = dt2.AsEnumerable.OrderBy(Function(r) String.Join(chr(9),r.ItemArray)).CopyToDataTable Dim idx1 As Int32 = dt1.Rows.Count-1 Dim idx2 As int32 = dt2.Rows.Count-1 While (idx1>=0 AndAlso idx2>=0) If (String.Join(chr(9),dt1.Rows(idx1).ItemArray)=String.Join(chr(9),dt2.Rows(idx2).ItemArray) ) dt1.Rows.Remove…

Regards,

[Dhruvi_Arumugam]

Ok Thanks,
also can we try with Inner Join ?

[Yoichi]

HI,

Do you want to compare whole row or specific column?
If former, I think we don’t need to use inner join. If latter, JoinDataTables activity might help you.

Regards,

[Dhruvi_Arumugam]

I want to compare the whole row like every cell of both the tables.

Thanks

[Dhruvi_Arumugam]

@Yoichi

Hi,

Do you mean you need to leave rows of dt2? If so, can you try the following expression?

One thing here, I don’t want to leave the rows. DT1 has say 100k rows i want to match it in DT2 and retrieve the matched data set and store it in a datatable. Example in DT1 if there are 100k rows it is possible that DT2 might have matching 110k rows which include duplicate. I want to retrieve that set.

thanks