Multiple Insert SQL Oracle

Best way to do multi-row insert in Oracle?

This works in Oracle:

insert into pager (PAG_ID,PAG_PARENT,PAG_NAME,PAG_ACTIVE)
          select 8000,0,'Multi 8000',1 from dual
union all select 8001,0,'Multi 8001',1 from dual

The thing to remember here is to use the from dual statement.

How do you insert multiple rows of data using SQL or PLSQL into a table in Oracle?

SQL Workshop, right? Include those commands into a begin-end block (and make them PL/SQL instead):

begin
  insert into historie ...;
  insert into historie ...;
end;
/

and then push the RUN button.

SQL INSERT INTO multiple rows

As far as I can see there is no syntax errors within my command

Actually, there is. Syntax you used is invalid, as far as Oracle is concerned.

---

'SQL commands' on Oracle Apex, which expects only one command at a time

Actually, no. You can run several of them, just not as SQL but PL/SQL which means that you have to enclose them into BEGIN-END block. Have a look at the screenshot:

Note, however, that not everything can be run that way. DDL would, for example, require dynamic SQL.

Inserting multiple rows in a single Oracle SQL query:

You where close, but you have much to learn.

Here is how you could do it:

INSERT INTO "SCOTT"."GREATCOLOR1" (COLOR, PAUL, JOHN, TIM, ERIC)
          select 'White', '1', '5', '1', '3' from dual
union all select 'Yello', '8', '4', '3', '5' from dual
union all select 'Black', '2', '2', '9', '1' FROM dual
;

How to insert multiple object rows into a table in oracle sql?

how do i insert the second row of "kids" (Leanardo, Trudeau, 10).

You cannot. The kids type is defined as:

CREATE TYPE kids AS OBJECT(firstK VARCHAR(20), lastK VARCHAR(20), age INT);

and then you define the table as:

CREATE TABLE person (
  id VARCHAR(10),
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  adr address,
  pn phoneNum,
  ks kids
);

kids (despite the plural in its name) is a singular object so you can only put a single kids value into it.

If you want to put multiple kids then you either need to:

• change the table to store a collection of kids;
• have a separate table for children; or
• reverse the relationship and store two (or more) foreign key relationships for parents.

For example, you could (since children are people too) store the relationship as:

CREATE TYPE address AS OBJECT(
  num    INT,
  street VARCHAR2(20), 
  city   VARCHAR2(20),
  zip INT
);
CREATE TYPE phoneNum AS VARRAY(2) OF VARCHAR2(13);

CREATE TABLE person (
  id            INTEGER
                CONSTRAINT person__id__pk PRIMARY KEY,
  firstName     VARCHAR2(20),
  lastName      VARCHAR2(20),
  date_of_birth DATE,
  adr           address,
  pn            phoneNum
);

CREATE TABLE children(
  id        INTEGER
            CONSTRAINT children__id__pk PRIMARY KEY,
  parent_id CONSTRAINT children__parent_id__fk REFERENCES person (id),
  child_id  CONSTRAINT children__child_id__fk  REFERENCES person (id),
  CONSTRAINT children__pid_cid__u UNIQUE (parent_id, child_id)
);

db<>fiddle here

Insert multiple rows with multiple values nextval Oracle SQL

Try this:

    insert into x
    select tt.* , test_id_seq.nextval from
    (select 12345, 'text' from dual
    union all
    select 23589, 'other text' from dual) tt;

SQL Multiple Insert into multiple rows

I don't think that Oracle supports VALUES with multiple records. Here is a simple alternative:

INSERT INTO MY_EMPLOYEE
    SELECT 126,'Popov', 'Olga', 'opopov', 8500 FROM DUAL UNION ALL
    SELECT 127, 'Chen', 'Ling', 'lcheng', 14500 FROM DUAL UNION ALL 
    SELECT 128, 'Dunn', 'David', 'ddunn', NULL FROM DUAL;

Note: I do highly recommend that you specify the columns for the insert, but that is another issue.

Multiple insert SQL oracle

EDIT Added two test cases, and a possible workaround.

Though Insert statement and insert all statement are practically the same conventional insert statement. But when it comes to sequences, they work differently.

Test case 1 : Identity columns

SQL> DROP TABLE table1 PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE Table1 (
  2    Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
  3    column3 NUMBER,
  4    PRIMARY KEY (Table1Id)
  5  );

Table created.

SQL>
SQL> INSERT ALL
  2    INTO Table1 (column3) VALUES ('1')
  3    INTO Table1 (column3) VALUES ('2')
  4  SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.SYS_C0010439) violated

SQL>

Let's see what's actually happening under the hood -

SQL> CREATE TABLE Table1 (
  2    Table1Id NUMBER GENERATED ALWAYS AS IDENTITY,
  3    column3 NUMBER,
  4    CONSTRAINT A UNIQUE (Table1Id)
  5  );

Table created.

SQL> INSERT ALL
  2    INTO Table1 (column3) VALUES (1)
  3    INTO Table1 (column3) VALUES (2)
  4  SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.A) violated

SQL> SELECT * FROM table1;

no rows selected

SQL> ALTER TABLE table1
  2  DISABLE CONSTRAINT a;

Table altered.

SQL> INSERT ALL
  2    INTO Table1 (column3) VALUES (1)
  3    INTO Table1 (column3) VALUES (2)
  4  SELECT * FROM dual;

2 rows created.

SQL> SELECT * FROM table1;

  TABLE1ID    COLUMN3
---------- ----------
         2          1
         2          2

SQL>

So, the sequence progressed to nextval however there was an unique constraint violation the first time we did an Insert All. Next, we disabled the unique constraint, and the subsequent Insert All reveals that the sequence did not progress to nextval, rather it attempted to insert duplicate keys.

Though the issue doesn't occur with a INSERT-INTO-SELECT statement.

SQL> INSERT INTO table1(column3) SELECT LEVEL FROM dual CONNECT BY LEVEL <=5;

5 rows created.

SQL>
SQL> SELECT * FROM table1;

  TABLE1ID    COLUMN3
---------- ----------
         2          1
         3          2
         4          3
         5          4
         6          5

SQL>

Surprisingly, as per the metadata, the sequence is supposed to proceed to nextval automatically, however it doesn't happen with an Insert All statement.

SQL> SELECT COLUMN_NAME,
  2    IDENTITY_COLUMN,
  3    DATA_DEFAULT
  4  FROM user_tab_cols
  5  WHERE table_name   ='TABLE1'
  6  AND IDENTITY_COLUMN='YES';

COLUMN_NAME     IDENTITY_COLUMN DATA_DEFAULT
--------------- --------------- ------------------------------
TABLE1ID        YES             "LALIT"."ISEQ$$_94458".nextval

SQL>

Test Case 2 : Using a sequence explicitly

The INSERT ALL would work the same way whether an identity column is used or an explicit sequence is used.

SQL> DROP SEQUENCE s;

Sequence dropped.

SQL>
SQL> CREATE SEQUENCE s;

Sequence created.

SQL>
SQL> DROP TABLE t PURGE;

Table dropped.

SQL>
SQL> CREATE TABLE t (
  2    ID NUMBER,
  3    text VARCHAR2(50),
  4    CONSTRAINT id_pk PRIMARY KEY (ID)
  5  );

Table created.

SQL>
SQL> INSERT ALL
  2    INTO t VALUES (s.nextval, 'a')
  3    INTO t VALUES (s.nextval, 'b')
  4    INTO t VALUES (s.nextval, 'c')
  5    INTO t VALUES (s.nextval, 'd')
  6  SELECT * FROM dual;
INSERT ALL
*
ERROR at line 1:
ORA-00001: unique constraint (LALIT.ID_PK) violated

SQL>
SQL> SELECT * FROM T;

no rows selected

SQL>
SQL> ALTER TABLE t
  2    DISABLE CONSTRAINT id_pk;

Table altered.

SQL> INSERT ALL
  2    INTO t VALUES (s.nextval, 'a')
  3    INTO t VALUES (s.nextval, 'b')
  4    INTO t VALUES (s.nextval, 'c')
  5    INTO t VALUES (s.nextval, 'd')
  6  SELECT * FROM dual;

4 rows created.

SQL> SELECT * FROM T;

        ID TEXT
---------- ----------------------------------------
         2 a
         2 b
         2 c
         2 d

SQL>

Possible workaround - Using a ROW LEVEL trigger

SQL> CREATE OR REPLACE TRIGGER t_trg
  2      BEFORE INSERT ON t
  3      FOR EACH ROW
  4      WHEN (new.id IS NULL)
  5      BEGIN
  6        SELECT s.NEXTVAL
  7        INTO   :new.id
  8        FROM   dual;
  9      END;
 10  /

Trigger created.

SQL> truncate table t;

Table truncated.

SQL> INSERT ALL
  2    INTO t (text) VALUES ('a')
  3    INTO t (text) VALUES ('b')
  4    INTO t (text) VALUES ('c')
  5    INTO t (text) VALUES ('d')
  6  SELECT * FROM dual;

4 rows created.

SQL> SELECT * FROM t;

        ID TEXT
---------- -------------------------
         3 a
         4 b
         5 c
         6 d

SQL>

---

Related Topics