I can not use the 'insert all' to insert values into the table

Question

I can not use the insert all to insert values into the first_name,last_name and phone columns.

CREATE TABLE accounts (
    account_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
    first_name VARCHAR2(25) NOT NULL,
    last_name VARCHAR2(25) NOT NULL,
    email VARCHAR2(100),
    phone VARCHAR2(12) ,
    full_name VARCHAR2(51) GENERATED ALWAYS AS( 
            first_name || ' ' || last_name
    ),
    PRIMARY KEY(account_id)
);

INSERT ALL
INTO accounts(first_name,last_name,phone)VALUES('John','Mobsey','410-555-0197')
INTO accounts(first_name,last_name,phone)VALUES('Ted','Scherbats','410-555-0198')
INTO accounts(first_name,last_name,phone)VALUES('Leeanna','Bowman','410-555-0199')
SELECT* FROM DUAL;

This is the error message I get when I try to Run the code:

ORA-00001: unique constraint (BTMDATABASE.SYS_C0086595925) violated ORA-06512: at "SYS.DBMS_SQL", line 1721

1. INSERT ALL
2. INTO accounts(first_name,last_name,phone)VALUES('Trinity','Knox','410-555-0197')
3. INTO accounts(first_name,last_name,phone)VALUES('Mellissa','Porter','410-555-0198')

Littlefoot · Accepted Answer · 2019-10-07T07:17:18.377

Exactly, you can not. The way you decided to create unique values for the account_id column won't work in insert all as all rows get the same value which violates the primary key constraint.

Two workarounds:

don't use insert all but separate insert statements
switch to a sequence in order to set primary key column's values

And, here's an example (if you need it):

SQL> create table accounts
  2    (account_id number primary key,
  3     first_name varchar2(20) not null
  4    );

Table created.

SQL> create sequence seq_acc;

Sequence created.

SQL> create or replace trigger trg_acc_seq
  2    before insert on accounts
  3    for each row
  4  begin
  5    :new.account_id := seq_acc.nextval;
  6  end;
  7  /

Trigger created.

SQL> insert all
  2    into accounts (first_name) values ('John')
  3    into accounts (first_name) values ('Ted')
  4    into accounts (first_name) values ('Leeanna')
  5  select * from dual;

3 rows created.

SQL> select * from accounts;

ACCOUNT_ID FIRST_NAME
---------- --------------------
         1 John
         2 Ted
         3 Leeanna

SQL>

You're welcome; I added an example, see if it helps. – Littlefoot Oct 07 '19 at 07:17 — Littlefoot, Oct 07 '19 at 07:17

MT0 · Answer 2 · 2019-10-07T07:31:40.807

Your statement:

INSERT ALL
INTO accounts(first_name,last_name,phone)VALUES('John','Mobsey','410-555-0197')
INTO accounts(first_name,last_name,phone)VALUES('Ted','Scherbats','410-555-0198')
INTO accounts(first_name,last_name,phone)VALUES('Leeanna','Bowman','410-555-0199')
SELECT* FROM DUAL;

Will try to give all the rows the same account_id which will violate your primary key.

Instead, you can use separate INSERT statements; or, if you want a single statement/transaction then you can wrap the INSERT statements in an anonymous PL/SQL block:

BEGIN
  INSERT INTO accounts(first_name,last_name,phone)VALUES('John','Mobsey','410-555-0197');
  INSERT INTO accounts(first_name,last_name,phone)VALUES('Ted','Scherbats','410-555-0198');
  INSERT INTO accounts(first_name,last_name,phone)VALUES('Leeanna','Bowman','410-555-0199');
END;
/

or, you can also use INSERT INTO ... SELECT ... UNION ALL ...:

INSERT INTO accounts(first_name,last_name,phone)
  SELECT 'Trinity', 'Knox',  '410-555-0197' FROM DUAL UNION ALL
  SELECT 'Mellissa','Porter','410-555-0198' FROM DUAL;

Output:

SELECT * FROM accounts;

ACCOUNT_ID | FIRST_NAME | LAST_NAME | EMAIL | PHONE        | FULL_NAME      
---------: | :--------- | :-------- | :---- | :----------- | :--------------
         2 | John       | Mobsey    | null  | 410-555-0197 | John Mobsey    
         3 | Ted        | Scherbats | null  | 410-555-0198 | Ted Scherbats  
         4 | Leeanna    | Bowman    | null  | 410-555-0199 | Leeanna Bowman 
         5 | Trinity    | Knox      | null  | 410-555-0197 | Trinity Knox   
         6 | Mellissa   | Porter    | null  | 410-555-0198 | Mellissa Porter

Note: account_id of 1 is the failed INSERT ALL.

db<>fiddle here

score 0 · Answer 3 · answered Oct 07 '19 at 07:21

INSERT INTO accounts(first_name,last_name,phone)VALUES('John','Mobsey','410-555-0197');
INSERT INTO accounts(first_name,last_name,phone)VALUES('Ted','Scherbats','410-555-0198');
INSERT INTO accounts(first_name,last_name,phone)VALUES('Leeanna','Bowman','410-555-0199');

I can not use the 'insert all' to insert values into the table

3 Answers3