?
Solved

INSERT then APPEND ONLY NEW DATA from  TABLE_A into TABLE_B

Posted on 2008-10-11
19
Medium Priority
?
941 Views
Last Modified: 2013-12-26
First INSERT data into TABLE_B from TABLE_A.  Subsquent INSERTs into TABLE_B should only APPEND new records from TABLE_A.  This is the requirement.

I first created a new table TABLE_B.  I have an existing table TABLE_A to which new records are constantly added.

After creating the new TABLE_B, I first inserted all the records from TABLE_A into TABLE_B.

I used the following INSERT statement in SQL*PLUS:

INSERT INTO TABLE_A(SELECT username, action, code
FROM TABLE_B
WHERE username NOT IN (SELECT * FROM X)
AND code NOT IN (SELECT * FROM Y))
ORDER BY 1, 2, 3;

COMMIT;

Total records: 21 inserted into TABLE_B from TABLE_A.

New records were added to TABLE_A - three new records.  Now the total records in TABLE_A is 24 but only THREE NEW RECORDS WERE ADDED.

I then did a second INSERT into TABLE_B from TABLE_A using the above INSERT statement a second time.

The second INSERT inserted all the rows from TABLE_A (TOTAL OF 24 RECORDS) into TABLE_B AND THE TOTAL RECORDS IN TABLE_B IS NOW 45 INSTEAD OF 24.

I did not get any ORACLE ERRORS after the second INSERT.

The requirement is that after the first INSERT into TABLE_B, only the new records from TABLE_A SHOULD BE APPENDED TO TABLE_B.

This is a high priority task.  So please respond ASAP.

Thanks.
0
Comment
Question by:as93is
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 6
  • 3
  • +1
19 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22695260
2 questions:

?  why not put a trigger on table_A, to insert the inserted rows immediately into table_b?
?  if you don?t want to use a trigger, what is the "primary key" of the table A ?
 
0
 

Author Comment

by:as93is
ID: 22695345
I CANNOT USE A TRIGGER on the TABLE_A because the table is owned by SYS.  Actually, TABLE_A is DBA_AUDIT_TRAIL data dictionary view owned by SYS.  THERE IS NO PRIMARY KEY FOR DBA_AUDIT_TRAIL VIEW.

PLEASE SEND THE SQL STATMENTS AS EXAMPLES TO COMPLETE THIS HIGH PRIORITY TASK.

Thanks!!!

CONGRATULATIONS ON GETTING THE HIGHEST POINTS!!!

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22695369
ok, if it's not a primary key, what is the combination of columns identifying if it's a new row, or if the row already exists?
do you know the MERGE statement?
www.psoug.org/reference/merge.html
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:as93is
ID: 22695777
TABLE_A has the TIMESTAMP column which helps to identify new records added to the table. TABLE_B also has a TIMESTAMP column.  The join on the two tables could be on the TIMESTAMP column.

I DID NOT WORK WITH MERGE STATEMENT.

I have run a shell script that will APPEND the new rows to TABLE_B through the cron every night.

Please send sample code/examples to append the new rows from TABLE_A  to TABLE_B.  

Thanks.

0
 
LVL 27

Expert Comment

by:sujith80
ID: 22696514
Are you going to manipulate the data in TABLE_B?
If not; why not use TABLE_A itslef for your application? Or a view on TABLE_A?
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22696636
ok:
INSERT INTO TABLE_A(SELECT username, action, code
FROM TABLE_B
WHERE username NOT IN (SELECT * FROM X)
AND code NOT IN (SELECT * FROM Y))
AND timestampcol > ( select max(timestampcol) from TABLE_A)
ORDER BY 1, 2, 3;
 
COMMIT;

Open in new window

0
 

Author Comment

by:as93is
ID: 22697380
I WILL NOT MANIPULATE the data in TABLE_B.

I need to use the MERGE statement for the following reasons:

1.  Everyday new records will be added to TABLE_A.

2.  Every night, the new records from TABLE_A must be appended to TABLE_B based on the
     TIMESTAMP column.  
    TIMESTAMP column is in both TABLE_A and TABLE_B.

3.  Every night, after the new records from TABLE_A must be appended to TABLE_B successfully,
     TABLE_A SHOULD BE TRUNCATED.

4.  I have to schedule two cron jobs run every night as follows

    1.  One cron job to append data to TABLE_B from TABLE_A.

    2.  The second cron job TRUNCATE TABLE_A after cron job one successfully completes.

I need an efficient solution for the above.  I think MERGE statement could be a good solution.  But I don't know the systax for the MERGE statement.

Please help with sample code/examples.

Thanks.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22697452
>> 3.  Every night, after the new records from TABLE_A must be appended to TABLE_B successfully,
     TABLE_A SHOULD BE TRUNCATED.

Doesnt it say that all the records in table A should be inserted into table B the next day, as you dont have any previous day's data in the table??
An insert into table B select * from table A should do.
0
 

Author Comment

by:as93is
ID: 22697716
I repeat the requirement.

Every night, after the new records from TABLE_A must be appended to TABLE_B successfully,
TABLE_A SHOULD BE TRUNCATED.

Your solution will not work.  

Every night through cron, the new records from TABLE_A must be appended successfully to TABLE_B.  This is the most important requirement.

Thanks.
0
 

Author Comment

by:as93is
ID: 22697907
"An insert into table B select * from table A should do." will not work for my requirement.

The requirement is NOT TO INSERT ALL RECORDS FORM TABLE_A INTO TABLE_B EVERY NIGHT.  This will make TABLE_B grow unnecessarily.

The requiremnt is to APPEND only new records from TABLE_A based on certain conditions into TABLE_B.

Thanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22698361
so, you want certain records to be copied from TableA to TableB (your initial insert), and then just truncate tableA....
so, where is the problem, then?
0
 

Author Comment

by:as93is
ID: 22698600
I created a new table TABLE_B. I have already done the initial insert into TABLE_B with records with certain conditions from TABLE_A.  

The requirement is to APPEND only new records from TABLE_A based on certain conditions into TABLE_B EVERY NIGHT THROUGH A CRON JOB.

The requirement is NOT TO INSERT ALL RECORDS FORM TABLE_A INTO TABLE_B EVERY NIGHT.  This will make TABLE_B grow unnecessarily.

Your solution is as follows:

angelIII:

INSERT INTO TABLE_A(SELECT username, action, code
FROM TABLE_B
WHERE username NOT IN (SELECT * FROM X)
AND code NOT IN (SELECT * FROM Y))
AND timestampcol > ( select max(timestampcol) from TABLE_A)
ORDER BY 1, 2, 3;
 
COMMIT;

Please clarify the following:

Will your solution given above (the INSERT statement), ONLY APPEND the new records based on the TIMESTAMP column along with other conditions from TABLE_A into TABLE_B EVERY NIGHT when the CRON JOB RUNS WITH YOUR INSERT STATEMENT?

If your INSERT statement solution ONLY APPENDS THE NEW RECORDS FROM TABLE_A TO TABLE_B EVERY NIGHT , THEN YOUR SOLUTION IS THE CORRECT SOLUTION.

I got another solution from sujith80 ("An insert into table B select * from table A should do." ).  At this point, I don't want to mix up the two solutions and get more confused.

I have 10-13-2008 deadline to meet.

Thanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22698605
during the day, will there we rows inserted into table_A with "old" timestamps?
no -> you don't need to worry at all, actually, about the timestamp, as you TRUNCATE/EMPTY table_A completely.

yes -> my INSERT was turned the wrong way round... I guess mislead by your text ...

INSERT INTO TABLE_B(SELECT username, action, code
FROM TABLE_A
WHERE username NOT IN (SELECT * FROM X)
AND code NOT IN (SELECT * FROM Y))
AND timestampcol > ( select max(timestampcol) from TABLE_B)
ORDER BY 1, 2, 3;
 
COMMIT;

Open in new window

0
 

Author Comment

by:as93is
ID: 22699535
At last, I could make my requirements clear to you.

"during the day, will there we rows inserted into table_A with "old" timestamps?  "

What do you mean by "old" timestamps?  

When you start the day, TABLE_A will be empty because it was TRUNCATED the previous night.

New records will be inserted into the EMPTY TABLE_A with the current date, that is, the SYSDATE when the day starts.

Please let me know if your final solution will APPEND new rows from TABLE_A to TABLE_B every night when the cron runs the INSERT statement.

Thanks!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22700757
>New records will be inserted into the EMPTY TABLE_A with the current date, that is, the SYSDATE when the day starts.

that answers my question with the "old timestamps": so you don't insert timestamp values of "older" dates. only the current day.


>Please let me know if your final solution will APPEND new rows from TABLE_A to TABLE_B every night when the cron runs the INSERT statement.
yes, it should.
in fact, as there are not "old" rows in Table_A due to the truncate, it cannot add "old" rows.
0
 
LVL 27

Expert Comment

by:sujith80
ID: 22702471
@as93is:
Logically; once you insert the data in table A to table B, and then clear table A;
the next day everything in table A are "new".

Just "SIMPLE" insert into table B select * from table A "WILL DO"!!!!
0
 
LVL 32

Accepted Solution

by:
awking00 earned 2000 total points
ID: 22704276
please try this

insert into table_B (select username, action, code from table_A where username not in (select username from X) and code not in (select code from Y) minus select username, action, code from table_B); 

Open in new window

0
 

Author Comment

by:as93is
ID: 22717602
awking00
Yours is the correct solution.  I did some extensive testing of all the solutions provided for this question. Your solution got me the correct output.  If max(timestamp) is used then only one new record is retrieved instead 10 new records.
Thanks!
0
 
LVL 32

Expert Comment

by:awking00
ID: 22723798
Glad to help.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background Still having to process all these year-end "csv" files received from all these sources (including Government entities), sometimes we have the need to examine the contents due to data error, etc... As a "Unix" shop, our only readily …
This video shows how to recover a database from a user managed backup
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question