Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

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

Whilst you are here, check out some content from the AskTom team: A justification for referential integrity constraints

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)

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.