Comparing tables 1

[rmkg]

Is there a 'compare table' command so you don't have to nominate every column individually? If not in ANSI, then specifically in Teradata SQL?
Thanks.

 

[tlbroadbent]

You can do a quick compare using the UNION operator. By default SQL eliminates duplicate rows when using UNION. The following query will return the primary keys of rows that don't match between the two tables.

I'm not sure if Teradata will support this syntax.

--Assume the tables have a primary key.
--This can be a simple or compaound key.
Select <Primary key column(s)>
From (
Select * From table1
Union
Select * From table2)
Group By <Primary key column(s)>
Having count(*)>1

If you want to see the complate row for each unmatched row in table1 you can do the following. Change the table name to table2 to see rows from that table.

Select * From table1
Join (
Select <Primary key column(s)>
From (
Select * From table1
Union
Select * From table2)
Group By <Primary key column(s)>
Having count(*)>1) qry

On table1.keycol1=qry.keycol1
--if th key os compound
And table2.keycol1=qry.keycol2
... Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[tdatgod]

Hi,
I should point out there is a Teradata Forum in Tek Tips.

As you point out the above SQL assumes a Unique Prime Key. Teradata support ANSI MULTI-SET tables and NUPI's.



select count(*) from t1;
select count(*) from t2;

if those are equal then you could

select count(*) from
(
Select * From t1
Union
Select * From t2
);

And this should be identical to the previous 2 numbers. If it is they are identical

Unfortunately I don't know how to identify the rows which are different if the counts are not equal.

Checktable ( a Teradata specific utility ) will only compare a table to it's Fallback copy, its indexes, and references to make sure there are no integrity problems within a table.

 

[r937]

disclaimer: i don't know teradata

> Unfortunately I don't know how
> to identify the rows which are different

if teradata supports ansi sql, the rows that are different are

select * from table1
minus
select * from table2

and also vice versa

if both MINUS operations come back empty, the tables have the same rows

MINUS and INTERSECT are like UNION, only different

rudy

http://rudy.ca/

 

[rmkg]

Good news and bad news.

r937 - you beauty! Works a treat.

tlbroadbent and tdatgod - union doesn't appear to work. May be a teradata-specific (or even Queryman? unlikely?) problem, which I may refer to the correct forum - error messages:
Syntax error, expected something like a Word between the ')' and 'Group'
Syntax error, expected something like a Word between the ')' and the ';'
respectively, if you would care to comment...

 

[tdatgod]

I guess you need to alias the derived table. I don't know if this is ANSI requirement or a Teradata requirement.

sel count(*) from
(select * from p1
union
select * from p2
) a;
^^^^
*** Query completed. One row found. One column returned.

Count(*)
-----------
2

sorry about that.

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


DOH! I guess I need a refresher course on SET logic.

Again the MINUS will only work if you don't have any duplicate rows which are allowed in ANSI MULTISET tables.

For years Teradata Didn't support Multi-set tables because it was true to the original Codd/Date relational model and how can a set contain the same value twice. Typically leads to referential integrity problems.

Here is what I mean about the multi-set tables.

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

ins p1 (1,0);
ins p1 (2,0);

Then insert the same rows again.

ins p1 (1,0);
ins p1 (2,0);

Then only insert one set of the rows into the second table.

ins p2 (1,0);
ins p2 (2,0);

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

select count(*) from p1;

*** Query completed. One row found. One column returned.

Count(*)
-----------
4

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

select count(*) from p2;

*** Query completed. One row found. One column returned.

Count(*)
-----------
2


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

sel count(*) from
(select * from p1
union
select * from p2
) a;

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

Count(*)
-----------
2

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

select * from p1
minus
select * from p2;

*** Query completed. No rows found.


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

select * from p2
minus
select * from p1;

*** Query completed. No rows found.


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

But we know the tables aren't identical because one has 4 rows and one only has 2. Therefore both the Union and the Minus give you the wrong answer that the tables are identical.


-------