how to compare data between tables and views row by row , column by column in the test and production environment
-
-
you can create a DB link from test to PROD.
do the minus of 2 tables to get the difference -
pls explain in detail and give me thy tbl structure ,relations if any and some data from the tblsOriginally posted by khushbubhallaComment
-
Originally posted by jigs
Hi
I dont want a generalised result , the minus query lists out al the differences.
here say if i have
table a (Test)
col1(Pk) col2(Businesske y) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA XXX 5 6
3 AAA YYY 3 3
4 EFG HIJ 4 5
table a (Prod)
col1(Pk) col2(Businesske y) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA YYY 5 6
3 AAA YYY 2 2
4 GFE JIH 7 8
if the structure looks some wat like this (My identifier are the business keys and not the Primary keys)
then i want the result dispalyed like this
Row existing in test and not in prod
4 EFG HIJ 4 5
Row existing in prod and not in test
4 GFE JIH 7 8
Rows Existing in both but not matching
2 BCA XXX 5 6 (Test)
2 BCA YYY 5 6 (Prod)
3 AAA YYY 3 3 (Test)
3 AAA YYY 2 2 (Prod)
i want to write a generic procedure for the same which can give me these results. the input of the procedure will be the table names , columns names and the Db link
Can u pls help me with thisComment
-
Originally posted by vijaydiwakar
Hi
I dont want a generalised result , the minus query lists out al the differences.
here say if i have
table a (Test)
col1(Pk) col2(Businesske y) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA XXX 5 6
3 AAA YYY 3 3
4 EFG HIJ 4 5
table a (Prod)
col1(Pk) col2(Businesske y) col3(Business key) col4 col5
1 ABC XYZ 1 2
2 BCA YYY 5 6
3 AAA YYY 2 2
4 GFE JIH 7 8
if the structure looks some wat like this (My identifier are the business keys and not the Primary keys)
then i want the result dispalyed like this
Row existing in test and not in prod
4 EFG HIJ 4 5
Row existing in prod and not in test
4 GFE JIH 7 8
Rows Existing in both but not matching
2 BCA XXX 5 6 (Test)
2 BCA YYY 5 6 (Prod)
3 AAA YYY 3 3 (Test)
3 AAA YYY 2 2 (Prod)
i want to write a generic procedure for the same which can give me these results. the input of the procedure will be the table names , columns names and the Db link
CAn u help me with thisComment
-
select * from table1 t1 ,table2 t2Originally posted by khushbubhalla
where t1.col2<>t2.col 2 and t1.col3<>t2.col 3
and so on add ur columns hereComment
-
Originally posted by vijaydiwakar
The query that u have given gives me a very generic result.
I want a procedure which takes the table name and the column names as input
and the out put is more like a report from where i can identify wat is the problem
If i list all the columns in the query it will give me the entire record set
this doesn solve my purposeComment
-
select col4,col5
from test
minus
select col4,col5
from prod;Comment
-
i want to make a procedure which takes the tablename,colum nnames as input.
from the system tables we somehow find the unique index on the table and then have generic joins on the same
i am not quite sure as to how will i come up with such a generic procedure.
I want a specific result set.Comment
-
Just An algo of wat i want to do and am not really sure how
Proc(tablename( to be compared) , columnnames(tha t need to be compared), Business keys (or saythe unique indexeshere its col2 and col3))
1) Do a record matching analysis
Find the no of records matching based on Business keys
that ll be done by putting a join some wat like this
select l.col2,l.col3,r .col2,r.col3,'T est' as TableName from testa l, proda r where l.col2 = r.col2 and l.col3 = r.col3 union
select l.col2,l.col3,r .col2,r.col3,'P rod' as TableName from testa l, proda r where l.col2 = r.col2 and l.col3 = r.col3
2) & 3) the records present in one prod not in test and vice versa can be found out using
select col2,col3,'Test ' as TABLENAME from ((select col2,col3 from testa minus (select l.col2,l.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3)))
UNION
select col2,col3,'Prod ' as TABLENAME from((select col2,col3 from proda minus (select r.col2,r.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3)))
4)find the records present in both but different
select col2,col3,'Test ' as TABLENAME from
((select l.col2,l.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3) minus
(select l.col2,l.col3 from testa l, Proda r where l.col2=r.col2 and l.col3=r.col3))
union
select col2,col3,'Prod ' as TABLENAME from
((select r.col2,r.col3 from testa l, proda r where l.col2 = r.col2 or l.col3 = r.col3) minus
(select r.col2,r.col3 from testa l, Proda r where l.col2=r.col2 and l.col3=r.col3))
after this i want to perform an analysis of the matching records on the non keys
that can be done by a join on all attributes.
end of algo
i want the entire thing to be generic , so that it can be used for any table
the no of columns and the business keys wil vary with every time
i want a generic way of taking the column names and the business keys as a string from user and put them in the queries in a very generic wayComment
-
Another option is to use a database data comparison tool such as Zidsoft CompareDataComment
-
The SET operations should really help you out:Originally posted by khushbubhalla
Try this in test environment.
[code=oracle]
declare
tab_name VARCHAR2(20);
col_name VARCHAR2(20);
emp_det emp%ROWTYPE;
BEGIN
tab_name:= &1;
col_name:= &2;
EXECUTE IMMEDIATE 'SELECT '|| col_name|| ' FROM '|| tab_name INTERSECT 'SELECT '|| col_name|| ' FROM '|| tab_name||'@db_ link' BULK COLLECT INTO emp_det;
END;
[/code]
The above code will get you similra records from two tables of test and production instances. Do it similarly for getting uncommon rows.Comment
Comment