SQL: How to tell the differences btw 2 tables

[mit...@omni.voicenet.com]

Is there some standard way to use SQL to tell the differences
between two tables? The two tables would have the same column
definitions, I just want to know what needs to be added/changed
to make the second table look like the first.

mit...@omni.voicenet.com [Mitch Rosenberg]

[rve...@pi.net]

In article <46pa3t$i...@news.voicenet.com>, <mit...@omni.voicenet.com> writes:
> Path:
news.pi.net!news.sara.nl!news.nic.surfnet.nl!howland.reston.ans.net!cs.utexas.e
du!news.sprintlink.net!news.voicenet.com!news
> From: mit...@omni.voicenet.com
> Newsgroups: comp.databases
> Subject: SQL: How to tell the differences btw 2 tables
> Date: 27 Oct 1995 00:45:49 GMT
> Organization: Modular Technology, Inc.
> Lines: 7
> Message-ID: <46pa3t$i...@news.voicenet.com>
> Reply-To: mit...@omni.voicenet.com
> NNTP-Posting-Host: philly234.voicenet.com
> X-Newsreader: IBM NewsReader/2 v1.09

Mitch,
Is there a CATALOG for the system you use? For example with IBM/DB2 you
can use the SYSTABLE/SYSCOLUMN tables wich contain the actual definitions
of tables created. In NS/SQL and ORACLE there are also CATALOG tables but
I don't know the names. Wich DBMS are you using?
rv...@pi.net [Ruud van Eijk]

[ESSA]

rve...@pi.net wrote:
: > Is there some standard way to use SQL to tell the differences

: > between two tables? The two tables would have the same column
: > definitions, I just want to know what needs to be added/changed
: > to make the second table look like the first.

: Is there a CATALOG for the system you use? For example with IBM/DB2 you

: can use the SYSTABLE/SYSCOLUMN tables wich contain the actual definitions
: of tables created. In NS/SQL and ORACLE there are also CATALOG tables but

No. Checking Catalog tables will show differences between the table structures
but the original post already stated that the tables have the same structure
- it's the difference in content (ie rows) that is required.

To tell the differences, there are really only 3 conditions to check for :
row in table 1 not in table2, row in table2 not in table1, row in both but
non-key columns contain different values. This could conceivably be done
with a Union ...

Select "Not In Table2", Table1.KeyCol
From Table1 Where Not Exists
(Select * From Table2 Where Table2.KeyCol = Table1.KeyCol)
Union
Select "Not In Table1", Table2.KeyCol
From Table2 Where Not Exists
(Select * From Table1 Where Table1.KeyCol = Table2.KeyCol)
Union
Select "Different column values", Table1.KeyCol
From Table1, Table2
Where Table1.KeyCol = Table2.KeyCol
And (
Table1.OtherCol1 <> Table2.OtherCol1
Or Table1.OtherCol2 <> Table2.OtherCol2
etc...
)

Some DBMS's may not support this use of a Union.

Of course, you could just dump them both to text files and do a Unix diff :-)

---
pwhit...@essa.com

[mit...@omni.voicenet.com]

>> Is there some standard way to use SQL to tell the differences
>> between two tables? The two tables would have the same column
>> definitions, I just want to know what needs to be added/changed
>> to make the second table look like the first.

>Is there a CATALOG for the system you use? For example with IBM/DB2 you
>can use the SYSTABLE/SYSCOLUMN tables wich contain the actual definitions
>of tables created. In NS/SQL and ORACLE there are also CATALOG tables but

>I don't know the names. Wich DBMS are you using?

The DBMS is WSQL, but the question is not how to compare the structure
of 2 DB's, but rather the underlying data.

mit...@omni.voicenet.com [Mitch Rosenberg]

[Jay Mikuta]

mit...@omni.voicenet.com wrote:
>Is there some standard way to use SQL to tell the differences
>between two tables? The two tables would have the same column
>definitions, I just want to know what needs to be added/changed
>to make the second table look like the first.
>

>mit...@omni.voicenet.com [Mitch Rosenberg]
>

I'm not really sure what you are asking for:

1) To show the difference in table/column definitions?
2) Difference in data stored in both tables?

If #1, look at your documentation for the system tables (meta-data)
for your database. You can develop SQL to compare two tables using
this information.

If #2, you can use SQL to see rows which don't have a match in one table
or the other:

Select col_a,col_b from table1
where not exists
(select * from table2
where table1.col_a = table2.col_a and
table1.col_b = table2.col_b)

Repeat for table2.

Are you looking for something else?

Good luck!
J.