Solved

Duplicate records in two tables

Posted on 2007-03-26
4
214 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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

751 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