Problem: Comparing two tables 
Author Message
 Problem: Comparing two tables

RE: Comparing two tables in (Sybase) SQL?

  I'm in the market for a method to compare two tables on the same SQL server.
It would save me a world of time and lot of aspirin if someone has run into
this before (and lets me know a solution).  I really just need to know
what rows are in one table that are not in the other table (and visa versa)
and rows with the same key columns values but difference data in the other
columns.
  I was hoping to find a subtract, difference or minus SQL statement that
might do the trick but Sybase doesn't seem to offer any of these features(?).

One good thing is always true:

        The two tables are identical except for the data itself.
        Same number and names of columns in each.

Thanks for any input,

John Dennis

----------------------------------

| GTE - Federal Systems Division |
| Montgomery, Alabama            |
----------------------------------



Mon, 07 Aug 1995 22:53:01 GMT
 Problem: Comparing two tables

Quote:
>Be careful when comparing 2-phase commit to replication servers. There
>is a significant difference that may or may not matter to you. The
>replication schemes that I have seen do NOT GUARANTEE that all copies of
>the database are exactly in sync all of the time. The fact that you
>did an insert into your local copy does not mean that all other local
>copies have the information yet. 2-phase commit ensures that all parties
>of the commit execute the transaction or none of them do. This is a much
>stronger constraint and one that may be important to your application.

>None the less, replication servers are very useful and powerful.

>Good luck,

Good point. I understand that the replication is only a snapshot in time and
the master will have different data than the replication. This would be
acceptable for many decision support applications. Not so if you need
true 2-phase.
Thx Dave L


Tue, 08 Aug 1995 04:10:47 GMT
 Problem: Comparing two tables

Quote:

>>Be careful when comparing 2-phase commit to replication servers. There
>>is a significant difference that may or may not matter to you. The
>>replication schemes that I have seen do NOT GUARANTEE that all copies of
>>the database are exactly in sync all of the time. The fact that you
>>did an insert into your local copy does not mean that all other local
>>copies have the information yet. 2-phase commit ensures that all parties
>>of the commit execute the transaction or none of them do. This is a much
>>stronger constraint and one that may be important to your application.

>>None the less, replication servers are very useful and powerful.

>>Good luck,
>Good point. I understand that the replication is only a snapshot in time and
>the master will have different data than the replication. This would be
>acceptable for many decision support applications. Not so if you need
>true 2-phase.

        My understanding of the Replication Server is that it offers a
whole spectrum of data integrity policies that the DBA can choose
from. The claim is that not all businesses need to guarantee
transactions up to the level of 2PC.
        Here's an example I heard last month at the Parallel and
Distributed Info. Systems Conference from a Sybase spokesman: Consider
a distributed networks with three nodes: W for warehouse, A for sales
site one and B for sales site two. Assume that W is responsible for
the onHand data for items in the warehouse. At the beginning of the
day the onHand amount for widgets gets replicated at A and B. Let's
say there are 100 widgets in stock. Later in the day, W goes down.
After this disaster, both A and B sell 100 widgets and queue up
messages to W saying to ship 100 widgets.
        This is not a decision support system problem. In fact it is
not even a business problem. It is just an anomoly that must be
handled. W ships 100 widgets to A's customer and decrements the onHand
value of widgets to zero. B's customer is sent a message saying that
the order of 100 widgets is on back order. Business goes on happily
and correctly without a 2PC.
                                                        --Sid

--

Data Parallel Systems, Inc.  ============||  DPSI  ||===============
4617 E. Morningside Drive                             (812) 334-8100
Bloomington, Indiana,  47408  USA               FAX:  (812) 334-8121



Tue, 08 Aug 1995 23:39:27 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. DAO code to compare two fields in two tables

2. Comparing two columns in two tables

3. Comparing two tables and appending data to destination table

4. Comparing table contents of two tables

5. comparing two databases on two different servers?

6. comparing two tables

7. Comparing two identical tables

8. Compare two tables

9. Comparing Two Tables for Inaccuracies

10. compare all the records of all the fields of two tables

11. comparing and altering two tables,Databases

12. Need help comparing number in two tables


 
Powered by phpBB® Forum Software