How to insert multiple insert sql statement

0
votes

There is a table Person(id, name). I am inserting more than 1000 records into person table. Both id and name should be unique. I wrote something like this

INSERT ALL 
       INTO PERSON (1, 'MAYUR')
       INTO PERSON (2, 'SALUNKE') 
       .....(1000 records)
SELECT * FROM DUAL;

I am getting unique constraint for name in this query. How do I know which record in particular is failing. All I see in logs is this

Error starting at line : 3 in command - ORA-00001: unique constraint (UN_PERSON_NAME) violated.

This does not tell the exact record which is duplicate.

3
sqloracle
Quite confuse with insert all then select *, if u really want to know which line have issue, better split these into separate line. - BeiBei ZHU
First, insert into temp table(temp_person) with no unique constraints, then group by name to see which name is duplicated - Natiq

3 Answers

2
votes

You are missing values keyword. Try this!

INSERT ALL 
       INTO PERSON values(1, 'MAYUR')
       INTO PERSON values(2, 'SALUNKE') 
       .....(1000 records)
SELECT * FROM DUAL;
-1
votes
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
-1
votes

Unfortunately, Oracle doesn't support multiple inserts using a single VALUES() statement. I usually approach this as:

INSERT PERSON (id, name)
    SELECT 1, 'MAYUR' FROM DUAL UNION ALL
    SELECT 2, 'SALUNKE' FROM DUAL UNION ALL 
       .....;

One advantage of this approach is you can use a subquery and assign the id:

INSERT PERSON (id, name)
    SELECT rownum, x.name
    FROM (SELECT 'MAYUR' FROM DUAL UNION ALL
          SELECT 'SALUNKE' FROM DUAL UNION ALL 
          .....
         ) x