Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Duplicate records in two tables

Posted on 2007-03-26
4
Medium Priority
?
216 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
[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 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 1000 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 1000 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

715 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