Solved

Insert/update statement in Oracle

Posted on 2014-10-06
3
327 Views
Last Modified: 2014-10-06
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
0
Comment
Question by:angel7170
  • 2
3 Comments
 
LVL 76

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),
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
 

Author Comment

by:angel7170
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
0
 
LVL 76

Accepted Solution

by:
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
T-SQL Convert to PL/SQL 23 80
sql for Oracle views 8 49
query in Oracle forms Builder 2 42
MULTIPLE DATE QUERY 15 52
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 In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

867 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now