Insert/update statement in Oracle

Posted on 2014-10-06
Last Modified: 2014-10-06

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

  RUN_SEQ_NO       NUMBER(10), ----Autonumber
  RUN_DT           DATE, -----sysdate
  TABLE_NAME       VARCHAR2(100 BYTE), ----table name
   LAST_UPDT_USER   VARCHAR2(50 BYTE),          

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
Question by:angel7170
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
  • 2
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40364230
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),

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.

Author Comment

ID: 40364272
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
LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 40364280
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle DR - data guard failover. 18 74
ER Diagram 3 52
SQL query for highest sequence 4 75
Query - Duplicate dates with different activities counts 10 58
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

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