Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Naga.

Asked: November 22, 2019 - 12:42 pm UTC

Answered by: Connor McDonald - Last updated: December 05, 2019 - 2:05 pm UTC

Category: PL/SQL - Version: 11

Viewed 1000+ times

You Asked

Hai bro,

How to insert a records for multiple tables with out using INSERT ALL and with single INSERT statement only, is that possible ?

and we said...

Nope.

Not unless you want start "fiddling" with code, eg, putting triggers on a table which does inserts into other tables etc. But with just plain SQL - that is what INSERT ALL is for.

and you rated our response

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Example needed

November 25, 2019 - 12:50 pm UTC

Reviewer: A reader

Can you give an example with practically bro ?
Connor McDonald

Followup  

November 29, 2019 - 4:49 am UTC

SQL> create table t1 ( x int);

Table created.

SQL> create table t2 ( x int );

Table created.

SQL> create table t3 ( x int );

Table created.

SQL>
SQL> create or replace
  2  trigger trg
  3  after insert on t1
  4  for each row
  5  begin
  6    insert into t2 values (:new.x);
  7    insert into t3 values (:new.x);
  8  end;
  9  /

Trigger created.

SQL>
SQL> insert into t1 values (1);

1 row created.

SQL> select * from t1;

         X
----------
         1

SQL> select * from t2;

         X
----------
         1

SQL> select * from t3;

         X
----------
         1


although it continues to mystify me why you would not use the features available to you

Yo "Bro"

November 26, 2019 - 8:34 am UTC

Reviewer: A reader

The least you can do is be at least cordial and address people properly.

Although "bro" is neither derogatory nor offensive but it is kinda/sorta not in line with the mutual respect that users display on this forum.

Cheers

Connor McDonald

Followup  

November 29, 2019 - 4:42 am UTC

+1

Unable to get the result for the below logic

December 04, 2019 - 10:45 am UTC

Reviewer: A reader

select (EXTRACT(HOUR FROM (to_timestamp(to_char(systimestamp, 'DD-MON-YY HH24.MI.SS'), 'yyyy-mm-dd hh24:mi:ss') -
to_timestamp(to_char(cast(new_time(M_timestamp2, 'GMT', 'PDT') as timestamp), 'DD-MON-YY HH24.MI.SS') , 'yyyy-mm-dd hh24:mi:ss'))DAY TO SECOND)) as RUN_TIME

When i am checking the columns separately i am getting the results.

select to_timestamp(to_char(systimestamp, 'DD-MON-YY HH24.MI.SS'), 'yyyy-mm-dd hh24:mi:ss') t1,
2 to_timestamp(to_char(cast(new_time(M_timestamp2, 'GMT', 'PDT') as timestamp), 'DD-MON-YY HH24.MI.SS') , 'yyyy-mm-dd hh24:mi:ss') t2
3

T1
---------------------------------------------------------------------------
T2
---------------------------------------------------------------------------
19-DEC-04 02.29.10.000000000 AM
19-DEC-03 08.03.42.000000000 PM


Chris Saxon

Followup  

December 05, 2019 - 2:05 pm UTC

I don't understand what you're hoping to happen here...



...or what this has to do with the original question!

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.