Solved

How to merge two datatables and remove duplicate rows

Posted on 2014-09-19
4
4,668 Views
Last Modified: 2014-10-01
I have two datatables with identical columns, the uniqueness is based on three columns
ID, Code and BusDate

How can I remove the duplicates
dt1.Merge(dt2) does not remove the duplicates because there are a total of six columns
0
Comment
Question by:countrymeister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40334254
Use an append query.

1.

Append all the rows in one table

2.

Now append all the rows in the second where there is NO match in those three columns (x != x1 and y != y1 and z != z1)
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 40334256
oops, use an OR not the and in the condition

Use an append query.
Append all the rows in one table
Now append all the rows in the second where there is NO match in those three columns (x != x1 or y != y1 or z != z1)
0
 
LVL 25

Expert Comment

by:apeter
ID: 40335651
Hope this helps.

var distinctItems = <yourDatamodelCollection>.Distinct();

To match on only some of the properties, create a custom equality comparer, e.g.:

class DistinctItemComparer : IEqualityComparer<Item> {

    public bool Equals(Item x, Item y) {
        return x.Id == y.Id &&
            x.Name == y.Name &&
            x.Code == y.Code &&
            x.Price == y.Price;
    }

    public int GetHashCode(Item obj) {
        return obj.Id.GetHashCode() ^
            obj.Name.GetHashCode() ^
            obj.Code.GetHashCode() ^
            obj.Price.GetHashCode();
    }
}
Then use it like this:

var distinctItems = <yourDatamodelCollection>.Distinct(new DistinctItemComparer());
0
 
LVL 11

Accepted Solution

by:
louisfr earned 500 total points
ID: 40336433
Set the PrimaryKey property of the DataTable objects. You can do that in the designer if you have a typed dataset, or do it in code:
dt1.PrimaryKey = new[] { dt1.Columns["ID"], dt1.Columns["Code"], dt1.Columns["BusDate"] };
dt2.PrimaryKey = new[] { dt2.Columns["ID"], dt2.Columns["Code"], dt2.Columns["BusDate"] };

Open in new window

You can then have dt2 rows overwrite rows from dt1 with the same keys:
dt1.Merge(dt2);

Open in new window

or have dt1 rows preserved:
dt1.Merge(dt2, true);

Open in new window

0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question