COMPARE TWO SIMILAR TABLES

[lyudmila]

I have two tables table A and table B. They keep identical data, same fields, same datatype.
Table A has 1000000 records.
Table B has 55000 records.
I need all records from table B which not exist in table A.
How I can do this?

 

[lespaul]

Try:

Select * from TableB where PKField not in (Select PKField from TableA)

where PKField is your primary key.

Leslie
landrews@metrocourt.state.nm.us

SELECT * FROM USERS WHERE CLUE > 0
No Rows Returned

 

[SQLSister]

HErs an example from two of my tables:
SELECT City, State, AirportName
FROM Airport LEFT OUTER JOIN
ApproachPlates ON Airport.AirportID = ApproachPlates.AirportID
WHERE (ApproachPlates.AirportID IS NULL)

 

[lyudmila]

My problem I have no PK in this tables. It's a client data, that I have to manupulate to our standard requirement. Files need to be as flat as possible, no normalization.
I have key fields to link tables, but they have a lot of duplicates.

 

[sunila7]

Hi,

u could also try this

Select * from TableB where not exists (Select PKField from TableA)


Sunil

 

[sunila7]

Hi,

Does this help....

Select Distinct * from TableB where not exists (Select PKField from TableA)


Sunil

 

[tlbroadbent]

Method 1:

Select *
From TableB b
Left Join TableA a
On b.col1=a.col1
And b.col2=a.col2
And b.col3=a.col3
And b.col4=a.col4
-- add as many columns as needed to identify rows
Where a.col1 is null

Method 2:
Select *
From TableB b
Where Not Exists
(Select * From TableA
Where col1=b.col1
And col2=b.col2
And col3=b.col3
And col4=b.col4)
-- add as many columns as needed to identify rows

To insure good performance, create indexes in TableA on the comparison columns. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/programmer.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[lyudmila]

Thank you very much to everyone who responded to my posted question.