Solved

Duplicate records in two tables

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now