compare the data of 2 tables are identical -- sql server programming

John Bell
8/6/2004 11:23:01 PM

Hi

You will select everything from tb1 is nothing exists in tb2. You will need
to add a where clause that compares each column of tb1 to the corresponding
column in tb2.
This will only compare data that is tb1 and not in tb2, there may be data
not in tb1 that is in tb2.

If you want more information please post DDL ( CREATE TABLE statements
etc...) and example data (as Insert statements).

John

[quoted text, click to view]

mark baekdal
8/6/2004 11:45:31 PM

you could try a tool that does it all for you
www.dbghost.com

[quoted text, click to view]

Steve Kass
8/7/2004 2:22:26 AM

The query you have written will return all rows from TB1 if TB2 is empty,
and no rows from TB1 if TB2 is not empty. You probably want something like

select * from DB1
where not exists (
select * from DB2
where DB2.Col1 = DB1.Col1
and DB2.Col2 = DB2.Col2
and ...
)

This will return a row from DB1 whenever there is not a matching row in DB2.
You can reverse the logic to find out what's in DB2 that isn't in DB1 and
combine the two with UNION ALL, and to see which table each is from, make it

select 'DB1' as Which, * from DB1
where not exists ...
....
union all
select 'DB2', * from DB2
....

Steve Kass
Drew University

[quoted text, click to view]

Joe Celko
8/7/2004 10:53:39 AM

1) You can get tools that do this.

2) You will get a lot of answers on this one, so let's play a bit and
see if we can do it without referring to columns and using JOINs.

CASE WHEN (SELECT COUNT(*) FROM DB1..TB1)
<> (SELECT COUNT(*) FROM DB2..TB2)
THEN 'No Match'
WHEN (SELECT COUNT(*)
FROM (SELECT * FROM DB1..TB1)
UNION
SELECT * FROM DB2..TB2) AS X)
<> (SELECT COUNT(*) FROM DB2..TB2)
THEN 'No Match'
WHEN (SELECT COUNT(*)
FROM (SELECT * FROM DB1..TB1)
UNION
SELECT * FROM DB2..TB2) AS X)
<> (SELECT COUNT(*) FROM DB1..TB1)
THEN 'No Match'
ELSE 'March!' END

The idea is that the first WHEN clause will be very fast and will
produce results that can be used in the next two WHEN clauses.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : compare the data of 2 tables are identical