Insert/update statement in Oracle

Hello,

I have a log table (DQ_LOG) with columns as below

CREATE TABLE DQ_LOG
(
  RUN_SEQ_NO       NUMBER(10), ----Autonumber
  RUN_DT           DATE, -----sysdate
  TABLE_NAME       VARCHAR2(100 BYTE), ----table name
  SOURCE_COUNT     NUMBER(20),
  TARGET_COUNT     NUMBER(20),
  S_MINUS_T_COUNT  NUMBER(20),
  T_MINUS_S_COUNT  NUMBER(20),
   LAST_UPDT_USER   VARCHAR2(50 BYTE),          
  LAST_UPDT_DATE   DATE
)

Open in new window


I want to fill this table with data that I calculate within scripts. I have a table name let's say (DQ_Action1) and for that I have the calculation script like below

Select count(*) from TableA  ---- This value should be updated in column SOURCE_COUNT    
Select count(*) from TableB ---- This value should be updated in column TARGET_COUNT    
Select count(*) from TableC ---- This value should be updated in column S_MINUS_T_COUNT  
Select count(*) from TableD---- This value should be updated in column T_MINUS_S_COUNT

So my expected results should be values like this for each table name
(1, '10/06/2014', 'DQ_ACTION1', 1234,123,45,69, USER, '10/6/2014')
(1, '10/06/2014', 'DQ_ACTION2', 1234,123,45,69, USER, '10/6/2014')
(1, '10/06/2014', 'DQ_ACTION3', 1234,123,45,69, USER, '10/6/2014')

Can someone please let me know how can I do that in SQL query?

Thank you
angel7170Asked:
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:
Maybe something like:

insert into dq_log (some_seq.nextval, sysdate,
  (select count(*) from tableA),
(select count(*) from tableB),
(select count(*) from tableC),
(select count(*) from tableD),
user,
sysdate);

sysdate and user are functions that return the current OS date and the username of the database users that has connected to the database.

Adjust those columns as appropriate.
0
angel7170Author Commented:
I am just concerned about the performance, since some of the tables are huge and I am wondering if there is any other possibility for this.

Thank you
0
slightwv (䄆 Netminder) Commented:
To get the count from a table, you need to count the rows.

NOW, if the count's don't need to be accurate there is a num_rows column in USER_TABLES (and the DBA and ALL views as well).  This column is only as good as the last time the statistics were updated.

It is a good 'estimate' but should not be relied on for 100% accuracy.
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
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.