Solved

Duplicate records in two tables

Posted on 2007-03-26
4
213 Views
Last Modified: 2010-03-19
Hello I have two tables, table A and table B.  
Table A has full list of customer names (nvarchar)
table B has new customer names (however some of these customers are already in table A.

I want to eliminate the records in Table B that are already in Table A, as otherwise when I insert the data in table B into Table A, duplicate record will emerge.

How can I process these two tables so that I can get rid of the duplicate records in Table B before the INSERT job?

Thanks
0
Comment
Question by:bedevi
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18795170


SELECT CustomerName
FROM TableA
UNION
SELECT CustomerName
FROM TableB
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 250 total points
ID: 18795189
delete from tableb
 where exists (select name from tableA as A where a.name = tableb.name)

would do it ...
but having name as a key is a silly way to design a table i trust you actually have proper keys in place...

also ideally you would just update a flag/processed date column on tableB to indicate that the  row should not
be considered for the update process...

alternatively

insert into tablea (column list)
 select (column list) from tableB as b
    where not exists (select name from tablea as a where b.name=a.name)

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 18795190
insert into TableA
SELECT CustomerName
FROM TableB b WHERE NOT EXISTS (SELECT 1 FROM TableA WHERE CustomerName = b.customerName)
0
 

Author Comment

by:bedevi
ID: 18803394
but having name as a key is a silly way to design a table i trust you actually have proper keys in place...

Oh no its just that I m working with a 3rd party with whom we could not agree on a  reciprocal unique numbering system yet, and what is more the other party is supplying not only new customers but the previous ones as well.  Cant do dates either cause.. anyway long story :) thanks a lot
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

820 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