You Asked
Hi,
Thanks for a forum like this to post our questions and get awesome solutions from you.
My question is
I have 2 huge tables (around 1 million records) TableA and TableB in the same structure. In each of this table I have 28 coulmns. Out of this 28 columns 1 column is primary key. I need to compare the two tables and display the result in the following format.
Note: I don’t have to report the columns that have not changed.
PK ColumnName OldValue NewValue
--------------------------------------------------------------------------
1 Column1 100 200
1 Column5 ABC XYZ
I have tried
(SELECT * FROM TableA
MINUS
SELECT * FROM TableB)
UNION ALL
(SELECT * FROM TableA
MINUS
SELECT * FROM TableB)
This is taking time and also I am not able to display the changes in the required format.
For Demo purpose, please use the following script
CREATE TABLE emp_new
(eno NUMBER PRIMARY KEY,
ename VARCHAR2(100),
salary NUMBER(13, 2),
Commission NUMBER(13, 2),
HireDate DATE,
Designation varchar(100)
);
CREATE TABLE emp_old
(eno NUMBER PRIMARY KEY,
ename VARCHAR2(100),
salary NUMBER(13, 2),
Commission NUMBER(13, 2),
HireDate DATE,
Designation varchar(100)
);
insert into emp_new
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(100, 'Name1', 1000, 0, '13-MAY-2001', 'CEO');
insert into emp_new
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(200, 'Name2', 2000, 0, '13-JUN-2001', 'ACCOUNTANT');
insert into emp_new
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(300, 'Name3', 3000, 0, '13-JUL-2001', 'SENIOR ANALYST');
insert into emp_new
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(400, 'Name4', 4000, 0, '13-AUG-2001', 'ANALYST');
insert into emp_new
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(500, 'Name5', 5000, 0, '13-SEP-2001', 'SALES MANAGER');
insert into emp_old
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(100, 'Name11', 1000, 0, '13-MAY-2011', 'CEO');
insert into emp_old
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(200, 'Name2', 2000, 0, '13-JUN-2001', 'ACCOUNTANT');
insert into emp_old
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(300, 'Name32', 3000, 0, '13-JUL-2011', 'SENIOR ANALYST');
insert into emp_old
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(400, 'Name4', 4000, 0, '13-AUG-2001', 'SENIOR ANALYST');
insert into emp_old
(eno, ename, salary, Commission, HireDate, Designation)
VALUES
(500, 'Name5', 10000, 0, '13-SEP-2001', 'SALES MANAGER');
Thanks in advance.
Regards
Abi
and Tom said...
One million records hasn't been huge for a long long time. In fact, I think of it as rather small - I use that many on my laptop for examples a lot.
See this article:
http://www.oracle.com/technetwork/issue-archive/2005/05-jan/o15asktom-084959.html It does not get the "precise" format you asked for - but it is very close and infinitely more compact/readable I believe. You can at least use that as a starting point for getting your ultimate result if you don't like my format.
Rating
(4 ratings)
Is this answer out of date? If it is, please let us know via a Comment