Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Insert/update statement in Oracle

Posted on 2014-10-06
3
Medium Priority
?
336 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 78

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 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

772 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