Monday, May 28, 2007

Multirow Inserts

While attempting to insert several rows into a table in our Oracle database, a colleague dutifully copied the exact ANSI/ISO SQL standard syntax for his purposes. Guess what happened?

INSERT INTO table (column1, column2)
VALUES (value1, value2), (value1, value2);

ERROR at line 1:
ORA-00933: SQL command not properly ended

Unlike some other databases (DB2, PostgreSQL, MySQL), Oracle doesn't support multirow inserts (yet). Instead, you need to execute these as separate statements.

INSERT INTO table (column1, column2)
VALUES (value1, value2);

1 row created.

INSERT INTO table (column1, column2)
VALUES (value1, value2);

1 row created.

Edit: Courtesy of Laurent Schneider (see the comments), here are two "tricks" in inserting several rows with the same statement.

Method #1:
INSERT ALL
INTO table (column1, column2)
VALUES (value1, value2)
INTO table (column1, column2)
VALUES (value1, value2)
...etc...
SELECT * FROM DUAL;

Method #2:
INSERT INTO table (column1, column2)
SELECT value1, value2 FROM DUAL UNION ALL
SELECT value1, value2 FROM DUAL UNION ALL
...etc...
SELECT value1, value2 FROM DUAL;

The moral of the story is not to expect Oracle to comply with the ANSI/ISO SQL standard every time. In some ways, like this one, they do not comply, and of course Oracle offers many extensions to the standard.

For more information, consult Appendix B of the Oracle SQL Reference, which deals very briefly with how your version of Oracle complies with the standard.

As for the ANSI SQL standard itself, I'm not aware of any free on-line source. If you want one, you'll have to purchase a copy directly from ANSI/ISO. There are different levels of standard compliance, Oracle qualifies as entry-level compliance. I don't know exactly what standards are entry-level and which ones aren't, but apparently multi-row inserts aren't.

Comments:
insert all
into table1(column1,column2)
values(1,2)
into table1(column1,column2)
values(3,4)
into table1(column1,column2)
values(5,6)
select * from dual;

3 row(s) inserted.
 
well, it is not really multirows inserts, it is rather multitable insert in the same table and could be achieved with

insert into table1(col1,col2)
select 1,2 from dual union all
select 3,4 from dual union all
select 5,6 from dual

anyway, it is not really related to ansi compliance. But I thought it was worth to mention,

Kind regards,
Laurent
 
Do you have a specific link for standards compliance of INSERT syntax?

Your link to the SQL Reference appears to be an 11MB pdf download.
 
No William.

I have neither a copy of the standard as it relates to INSERT, nor Oracle documentation that addresses this specifically.

I'm basing my understanding of the former on my colleague's textbook.

I would LOVE to have it ...
 
This comment has been removed by a blog administrator.
 
insert all
into table1(column1,column2)
values(1,2)
into table1(column1,column2)
values(3,4)
into table1(column1,column2)
values(5,6)
select * from dual;

does this one work with old oracle version like 8i
 
Ummmm, I really like your post, I have been looking for the information for long time, thank you for helong me out.
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?