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.
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.
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'
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.
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)