Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to merge two datatables and remove duplicate rows

Posted on 2014-09-19
4
Medium Priority
?
4,923 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

730 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