Comparing Data in two SQL Tables

Question

Comparing Data in two SQL Tables

HookSQLDBA7

SSChampion

Points: 10709
More actions
April 27, 2005 at 7:01 am

#85963

Is there any way in SQL Server to compare the column values in two tables and then print the rows that do not have matching values? Is there a compare function or would I have to read each table into a Cursor and compare columns from each table? Could I use a Select Statement with Subqueries? Also, how would Null Values be handle when compared? Please provide any examples you may have.
Thanks in advance, Kevin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 1

G'day,

There are a few ways to accomplish what you are asking. A few more details might point towards a specific solution. A common method is something like the following pseudocode:

SELECT *

FROM Table1 T1

FULL OUTER JOIN Table2 T2 ON T1.ID = T2.ID

WHERE T2.Column1 <> T1.Column1

OR T2.Column2 <> T1.Column2

This simple example can be readily expanded. There are also several commercial products that support a data diff capability. I uses Adept SQLDiff.

Hope this helps

Wayne

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 2

Red Gate software has a low cost compare tool, advertised in the footer, which is highly rated and there is a review on the site. ApexSQL also has one.

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 3

The above provided query is not working for me.

I need to compare the shares, price and market_value columns of the two tables. The below query is not working, it is returning 19500 records (each table only has 15002 records). Here are my efforts so far:

--Compares the production holdings data to the raw data for a specific date.

SELECT P.*, R.*

FROM manager_holdings_prod P

--INNER JOIN manager_holdings_raw R

--LEFT OUTER JOIN manager_holdings_raw R

FULL OUTER JOIN manager_holdings_raw R

ON

(P.dtser = R.dtser

AND P.managerID = R.managerID

AND P.cusip = R.cusip)

WHERE P.dtser = 20050426.0

AND R.dtser = 20050426.0

AND R.shares <> P.shares

OR R.price <> P.price

OR R.mkt_value <> P.mkt_value

FYI: dtser is a date field defined as a float. Also, the dtser, managerID and cusip are defined as an index on each table.

Thanks in advance, Kevin

Wayne Lawton Hall of Fame Points: 3532 More actions · Answer 4

Hello again,

Can you please post the actual DDL for the tables? I'll generate some dummy data and post the working query(s).

Thanks

Wayne

HookSQLDBA7 SSChampion Points: 10709 More actions · Answer 5

I have just discoverd that my two tables contain duplicate records. I think this is why the code is not working properly. The tables have an index but they do not have a primary key for business reasons.