Home » SQL & PL/SQL » SQL & PL/SQL » multiple inserts without Loops
multiple inserts without Loops [message #679575] Tue, 10 March 2020 05:37 Go to next message
ghostman
Messages: 16
Registered: October 2019
Junior Member
I have the following table table_1 which contains thousands of rows:

    Num_replication   object_name
    --------------------------------
           4             obj_1
           8             obj_2
          12             obj_3
for each of one of these rows, I need to insert rows in other tables.

For instance, I have to insert one row in table_2 for each row in table_1 :

    ID       obj_name
    ------------------
    1         obj_1
    2         obj_2
    3         obj_3
and in table_3 I have to insert number of rows based on num_replication as following:
    ID       port
    --------------
    1        P0001
    1        P0002
    1        P0003
    1        P0004
    2        P0001
    2        P0002
    2        P0003
    2        P0004
    2        P0005
    2        P0006
    2        P0007
    2        P0008
and the same for other rows.


I know that I can accomplish this using loops , but I need to do it without loops , using multiple inserts.

any help would be appreciated.

[Updated on: Tue, 10 March 2020 05:58]

Report message to a moderator

Re: multiple inserts without Loops [message #679577 is a reply to message #679575] Tue, 10 March 2020 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 67135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Thu, 10 October 2019 16:56

If you post a working Test case: create table and insert statements along with the result you want with these data then we will be able to work with your table and data.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.

[Updated on: Tue, 10 March 2020 06:09]

Report message to a moderator

Re: multiple inserts without Loops [message #679578 is a reply to message #679575] Tue, 10 March 2020 06:10 Go to previous message
_jum
Messages: 570
Registered: February 2008
Senior Member
Where do you have problems, what did you code yet?
You need a row generator and a little SQL.
Previous Topic: Analytic Function: How to Partition Hierarchical Data
Next Topic: MATERIALIZED VIEW REFRESH HISTORY
Goto Forum:
  


Current Time: Thu May 28 17:47:43 CDT 2020