Solved

Insert/update statement in Oracle

Posted on 2014-10-06
3
324 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.

Join & Write a Comment

Suggested Solutions

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to take different types of Oracle backups using RMAN.

707 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

20 Experts available now in Live!

Get 1:1 Help Now