How to compare 2 table's data?

[Former Member]

I have 2 IQ ( 12.7 and 16.0 ).

How to compare the specific table to make sure all the data are same?

row count(*) are the same, I want to compare all the columns.

[pawan_amarnani · Accepted solution]

Hello,

use SE16n & download it in excel & Do comparison.

[Former Member · Accepted solution]

I have 100000000+ rows

[Former Member]

HowTo: Comparing two tables and returning the # of rows that match FAST | Jason L. Froebe - Tech tip...

Load the table from IQ 12.7 into 16.0 and run a simple query:

The most intuitive way to write a comparison of two tables and spit out the # of rows that are the same involves a left join:

select 'Investment' as TableName, count(*) as RowCount

from Investment_A a, Investment_B b

where

  a.col1 = b.col1

  AND a.col2 = b.col2

  AND a.col3 = b.col3

  AND a.col4 = b.col4

This returns the correct answer but is very slow. Is there a better way? Of course!

select 'Investment' as TableName, count(*) as RowCount

from

(

select 1 as num

FROM (

  select * from Investment_A

  UNION ALL

  select * from Investment_B

) tmp

  GROUP BY

    col1, col2, col3, col4

  HAVING COUNT(0*) > 1

) tmp2

By pushing the comparison off into the GROUP BY, we leverage the DBMS engine far more efficiently.

There are two drawbacks:

1) Readability of the SQL code

2) Far more temporary storage is used for the GROUP BY. There is a real risk of running out of temporary storage if the tables are large.

[jong-kil_park]

Hi,

I think a couple of ways to compare the two tables.

Firstly, you can extract the tables one by one and comare the extracted flat file size.

In case it's rather big, then you can extract them sequentially by specifying rowid() when extracting.

Secondly, I think you can execute a query which implement a minus operation as below because they should have the same schema.

Because minus operation is not supported by IQ, you can implement it as below.

There should be 0 rows returned against this query.

I think you can repeat the query for all columns in the table.

This is supposedly that the two tables below, supplier and supplier2 has the same schema.

select s_suppkey, s_name, s_address, s_nationkey, s_phone, s_acctbal, s_comment

from ( select A.s_suppkey, A.s_name, A.s_address, A.s_nationkey, A.s_phone, A.s_acctbal, A.s_comment

       from supplier A left outer join supplier2 B

       on A.s_suppkey=B.s_suppkey) tmp

where  s_suppkey is NULL

Best Regards

Jerry

[Former Member]

I have not tested it but you could merge the 2 tables by key value and add compare indexes on all the matching datatypes. A simple query would then give the differnces and it should be fast since the index has already done the work

regards

Johan Bornman

[Former Member]

RS DA is one option

[c_baker]

You can't use SRS DA.  Can't replicate from IQ.

Chris