Select only the new values from a table

Hi experts,

Suppose you have Table AAA That has 5 Records.

Drop Table AAA;

Create Table AAA
(Col_1        Number,
Col_2        Varchar2 (50),
Col_3        Date);

Insert Into AAA Values (1, 'Rec_01', '01-JAN-2015');
Insert Into AAA Values (2, 'Rec_02', '01-JAN-2015');
Insert Into AAA Values (3, 'Rec_03', '01-JAN-2015');
Insert Into AAA Values (4, 'Rec_04', '01-JAN-2015');
Insert Into AAA Values (5, 'Rec_05', '01-JAN-2015');

Commit;

Drop Table BBB;

Create Table BBB
(Col_1        Number,
Col_2        Varchar2 (50),
Col_3        Date);

Insert Into BBB Values (1, 'Rec_01', '01-JAN-2015');
Insert Into BBB Values (2, 'Rec_02', '01-JAN-2015');
Insert Into BBB Values (3, 'Rec_03', '01-JAN-2015');
Insert Into BBB Values (4, 'Rec_04', '01-JAN-2015');
Insert Into BBB Values (5, 'Rec_05', '01-JAN-2015');

Commit;

Next Iteration, 5 more records were added to Table AAA, like below

Insert Into AAA Values (6, 'Rec_01', '01-FEB-2015');
Insert Into AAA Values (7, 'Rec_02', '02-FEB-2015');
Insert Into AAA Values (8, 'Rec_03', '03-FEB-2015');
Insert Into AAA Values (9, 'Rec_04', '04-FEB-2015');
Insert Into AAA Values (10, 'Rec_05', '05-FEB-2015');

Commit;

Question is: write ONE sql statement that brings ALL the "NEW" records From Table AAA into Table BBB. So that this statement can be run EVERY time there are new recoords in Table AAA, to be transfered to Table BBB.

Another  5 records to be inserted in Table AAA
Insert Into AAA Values (11, 'Rec_01', '11-FEB-2015');
Insert Into AAA Values (12, 'Rec_02', '12-FEB-2015');
Insert Into AAA Values (13, 'Rec_03', '13-FEB-2015');
Insert Into AAA Values (14, 'Rec_04', '14-FEB-2015');
Insert Into AAA Values (15, 'Rec_05', '15-FEB-2015');

No we do not want to create a Trigger.
Kamal AgnihotriAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
>>So that this statement can be run EVERY time there are new recoords in Table AAA

EVERY time requires some event tracking.  In this case, it is a trigger.

Since you don't want a trigger, how do you think you can figure out when DML transactions have happened?

As far as getting the 'missing' data for ALL rows missing:
insert into tableBBB (
select * from tableAAA
minus
select * from tableBBB
);
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gerwin Jansen, EE MVETopic Advisor Commented:
Are you deleting as well in table AAA?
0
johnsoneSenior Oracle DBACommented:
What about a merge statement?  Wouldn't that work?  The reason I question it is I thought someone else would have suggested that.

I didn't check this, but it seems like it should work to me:

merge into tablebbb b
using (select col_1, col_2, col_3 from tableaaa a)
on (a.col_1 = b.col_1)
when not matched then insert (b.col_1, b.col_2, b.col_3)
values (a.col_1, a.col_2, a.col_3);
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
I had a chance to test the merge statement and this seems to work
merge INTO bbb b 
USING (SELECT col_1, 
              col_2, 
              col_3 
       FROM   aaa) a 
ON (a.col_1 = b.col_1) 
WHEN NOT matched THEN 
  INSERT (b.col_1, 
          b.col_2, 
          b.col_3) 
  VALUES (a.col_1, 
          a.col_2, 
          a.col_3); 

Open in new window

Using the test case supplied by the author, I inserted rows into the AAA table only.  Then used the merge statement to copy them to BBB.  Ran in batches of 5 as shown in original example.

Not sure of the efficiency of any of these methods, but they should all work.
0
Mark GeerlingsDatabase AdministratorCommented:
I agree that either the merge or the "insert ... select... minus..." approach should work.  Which of those two approaches will work best or fastest in your system, with your: data volumes, database version, indexes, server and storage hardware,  Oracle init* parameters, etc.?  We don't know.  So, try them both.

Ideally the "date" column in your table is actually a "created_date" column that accurately reflects the actual date and time the record was created, *AND* to make this data copy process as fast as possible, ideally that date column is indexed in both of your tables.  Then, you could create a small control table where you record the highest value for this date in table AAA whenever you run this process, so you don't have to re-read all of the records in table 'AAA' every time.  Or, this could be a history table where you save the highest value for this date column from table AAA each time, maybe along with how many rows got copied each time, and what time it actually was when your data copy job ran.
0
Kamal AgnihotriAuthor Commented:
Hi Slightwv, Your suggestion worked. Thanks a lot.
0
slightwv (䄆 Netminder) Commented:
I would also test the MERGE example above to see which one performs better for you.

I normally jump to the MERGE command on questions like these.  Not sure why I went with minus first.

If after testing MERGE, you decide it is faster, just post back here and I can reopen this for you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.