Best way to compare contents of two tables?

Database

Best way to compare contents of two tables?

When you need to compare two tables to see what the differences are, are there any tools or shortcuts you use, or do you handcode the SQL to compare the two tables.

Background: In my SQL Server environment, I created a stored procedure which inspects the metadata of the two tables/views, creates a query (as dynamic sql) which joins the two tables on the specified key columns, and compares data in the compare columns, reporting key differences and data differences. The query can either be printed and modified/copied or just excecuted as is. We are not allowed to create stored procedures in our Teradata environment, unfortunately.
Tags (1)
5 REPLIES
Enthusiast

Re: Best way to compare contents of two tables?

There are a number of tables, views and macros available at DBC user level. I am not sure if you have access to all these, I use "Teradata Administrator" and menu option Database, List all objects. I connected as dbc to view them.

Do you think this will help: select * from dbc.columnstats order by databasename, tablename

You can get particulars for any table and compare.
Teradata Employee

Re: Best way to compare contents of two tables?

I think that the code below could help you.

SEL
A.TABLENAME TABELA_DESTINO,
A.COLUMNNAME COLUNA_DESTINO,
A.COLUMNFORMAT TIPO_COLUNA_DESTINO,
COALESCe(B.TABLENAME, A.COMMENTSTRING) TABELA_ORIGEM,
COALESCE(B.COLUMNNAME, A.COMMENTSTRING) COLUNA_ORIGEM,
COALESCE(B.COLUMNFORMAT, A.COLUMNFORMAT) TIPO_COLUNA_ORIGEM,
CASE WHEN
TIPO_COLUNA_DESTINO = TIPO_COLUNA_ORIGEM THEN 1
ELSE 0
END TIPO_COLUNA_OK
FROM
DBC.COLUMNS A
LEFT JOIN DBC.COLUMNS B
ON A.DATABASENAME = B.DATABASENAME
AND A.COLUMNNAME = B.COLUMNNAME
AND A.TABLENAME = 'TESTE_0001'
AND B.TABLENAME = 'TESTE_0002'

Something like that.

Teradata Employee

Re: Best way to compare contents of two tables?

Hi,

I couldn't understand exactly what you are trying to do .... match the columns and data-types .... or match data for given tables perhaps to mark rows for insert, update and/or delete.

If the case is the 1st one .... you can simply use the DBC tables as mentioned in 1st reply.

For the 2nd case, you need to know the PK and major-columns for the given tables, and you can store that information in a table and generate SELECT or INSERT statement using it. PK columns to join both tables and major-columns to check for changes against.

HTH!

Regards,

MAC

Re: Best way to compare contents of two tables?

just do simple minus query as your start.

sel * from table1
minus
sel * from table2;

and vice versa

if both results show zero rows then u have same data in both tables.

Re: Best way to compare contents of two tables?

Please help me for the following scenario.
I have 2 tables.
1. Table 1 with column 1 to column 10
2. Table 2 with column 1 to column 10

By Comparing both tables i got more than 100 records difference. So i took each record (Row) from the difference by comparing all 10 columns one by one manually. Is there any way that i can compare and get the details quickly.
E.g. Row 1 has the difference in column 7. (Instead checking all columns from the difference)

Many thanks in advance for the help.