Best way to do multi-row insert in Oracle?

In Oracle, to insert multiple rows into table t with columns col1, col2 and col3 you can use the following syntax:

INSERT ALL   INTO t (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')   INTO t (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')   INTO t (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')   .   .   .SELECT 1 FROM DUAL;

This works in Oracle:

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

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

Use SQL*Loader. It takes a little setting up, but if this isn't a one off, its worth it.

Create Table

SQL> create table ldr_test (id number(10) primary key, description varchar2(20));Table created.SQL>

Create CSV

oracle-2% cat ldr_test.csv1,Apple2,Orange3,Pearoracle-2% 

Create Loader Control File

oracle-2% cat ldr_test.ctl load data infile 'ldr_test.csv' into table ldr_test fields terminated by "," optionally enclosed by '"'               ( id, description )oracle-2% 

Run SQL*Loader command

oracle-2% sqlldr <username> control=ldr_test.ctlPassword:SQL*Loader: Release 9.2.0.5.0 - Production on Wed Sep 3 12:26:46 2008Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.Commit point reached - logical record count 3

Confirm insert

SQL> select * from ldr_test;        ID DESCRIPTION---------- --------------------         1 Apple         2 Orange         3 PearSQL>

SQL*Loader has alot of options, and can take pretty much any text file as its input. You can even inline the data in your control file if you want.

Here is a page with some more details -> SQL*Loader