Duplicate records in two tables

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
bedeviAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:


SELECT CustomerName
FROM TableA
UNION
SELECT CustomerName
FROM TableB
0
LowfatspreadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
insert into TableA
SELECT CustomerName
FROM TableB b WHERE NOT EXISTS (SELECT 1 FROM TableA WHERE CustomerName = b.customerName)
0
bedeviAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.