Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Naga.

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

Last updated: December 05, 2019 - 2:05 pm UTC

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 Connor 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.

Rating

  (3 ratings)

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

Comments

Example needed

A reader, November 25, 2019 - 12:50 pm UTC

Can you give an example with practically bro ?
Connor McDonald
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"

A reader, November 26, 2019 - 8:34 am UTC

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
November 29, 2019 - 4:42 am UTC

+1

Unable to get the result for the below logic

A reader, December 04, 2019 - 10:45 am UTC

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
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 demos

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

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library