Best way to do multi-row insert in Oracle

Question

I m looking for a good way to perform multi-row inserts into an Oracle 9 database   The following works in MySQL but doesn t seem to be supported in Oracle   INSERT INTO TMP DIM EXCH RT   EXCH WH KEY    EXCH NAT KEY    EXCH DATE  EXCH RATE    FROM CURCY CD    TO CURCY CD    EXCH EFF DATE    EXCH EFF END DATE    EXCH LAST UPDATED DATE   VALUES      1  1   28-AUG-2008   109 49   USD    JPY    28-AUG-2008    28-AUG-2008    28-AUG-2008         2  1   28-AUG-2008    54   USD    GBP    28-AUG-2008    28-AUG-2008    28-AUG-2008         3  1   28-AUG-2008   1 05   USD    CAD    28-AUG-2008    28-AUG-2008    28-AUG-2008         4  1   28-AUG-2008    68   USD    EUR    28-AUG-2008    28-AUG-2008    28-AUG-2008         5  1   28-AUG-2008   1 16   USD    AUD    28-AUG-2008    28-AUG-2008    28-AUG-2008         6  1   28-AUG-2008   7 81   USD    HKD    28-AUG-2008    28-AUG-2008    28-AUG-2008

User · Answer

If you have the values that you want to insert in another table already, then you can Insert from a select statement.

INSERT INTO a_table (column_a, column_b) SELECT column_a, column_b FROM b_table;

Otherwise, you can list a bunch of single row insert statements and submit several queries in bulk to save the time for something that works in both Oracle and MySQL.

@Espo's solution is also a good one that will work in both Oracle and MySQL if your data isn't already in a table.

User · Answer

Whenever I need to do this I build a simple PL SQL block with a local procedure like this   declare    procedure ins    is        p exch wh key INTEGER          p exch nat key INTEGER          p exch date DATE  exch rate NUMBER          p from curcy cd VARCHAR2          p to curcy cd VARCHAR2          p exch eff date DATE          p exch eff end date DATE          p exch last updated date DATE      begin       insert into tmp dim exch rt         exch wh key          exch nat key          exch date  exch rate          from curcy cd          to curcy cd          exch eff date          exch eff end date          exch last updated date         values        p exch wh key          p exch nat key          p exch date  exch rate          p from curcy cd          p to curcy cd          p exch eff date          p exch eff end date          p exch last updated date      end  begin    ins  1  1   28-AUG-2008   109 49   USD    JPY    28-AUG-2008    28-AUG-2008    28-AUG-2008       ins  2  1   28-AUG-2008    54   USD    GBP    28-AUG-2008    28-AUG-2008    28-AUG-2008       ins  3  1   28-AUG-2008   1 05   USD    CAD    28-AUG-2008    28-AUG-2008    28-AUG-2008       ins  4  1   28-AUG-2008    68   USD    EUR    28-AUG-2008    28-AUG-2008    28-AUG-2008       ins  5  1   28-AUG-2008   1 16   USD    AUD    28-AUG-2008    28-AUG-2008    28-AUG-2008       ins  6  1   28-AUG-2008   7 81   USD    HKD    28-AUG-2008    28-AUG-2008    28-AUG-2008    end

User · Answer

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

User · Answer

Use SQL Loader  It takes a little setting up  but if this isn t a one off  its worth it   Create Table  SQL gt  create table ldr test  id number 10  primary key  description varchar2 20    Table created  SQL gt    Create CSV  oracle-2  cat ldr test csv 1 Apple 2 Orange 3 Pear oracle-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  lt username gt  control ldr test ctl Password   SQL Loader  Release 9 2 0 5 0 - Production on Wed Sep 3 12 26 46 2008  Copyright  c  1982  2002  Oracle Corporation   All rights reserved   Commit point reached - logical record count 3   Confirm insert  SQL gt  select   from ldr test           ID DESCRIPTION ---------- --------------------          1 Apple          2 Orange          3 Pear  SQL gt    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

User · Answer

you can insert using loop if you want to insert some random values   BEGIN      FOR x IN 1    1000 LOOP          INSERT INTO MULTI INSERT DEMO  ID  NAME           SELECT x   anyName  FROM dual      END LOOP  END

User · Answer

In my case  I was able to use a simple insert statement to bulk insert many rows into TABLE A using just one column from TABLE B and getting the other data elsewhere  sequence and a hardcoded value     INSERT INTO table a       id      column a      column b       SELECT         table a seq NEXTVAL          b name          123     FROM         table b b    Result   ID  NAME  CODE  1  JOHN  123 2  SAM  123 3  JESS  123   etc

User · Answer

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

User · Answer

Cursors may also be used  although it is inefficient   The following stackoverflow post discusses the usage of cursors    INSERT and UPDATE a record using cursors in oracle

User · Answer

Here is a very useful step by step guideline for insert multi rows in Oracle   https   livesql oracle com apex livesql file content BM1LJQ87M5CNIOKPOWPV6ZGR3 html  The last step   INSERT ALL    Everyone is a person  so insert all rows into people    WHEN 1 1 THEN INTO people  person id  given name  family name  title  VALUES  id  given name  family name  title     Only people with an admission date are patients    WHEN admission date IS NOT NULL THEN INTO patients  patient id  last admission date  VALUES  id  admission date     Only people with a hired date are staff    WHEN hired date IS NOT NULL THEN INTO staff  staff id  hired date  VALUES  id  hired date    WITH names AS       SELECT 4 id   Ruth  given name   Fox  family name   Mrs  title             NULL hired date  DATE 2009-12-31  admission date     FROM   dual UNION ALL     SELECT 5 id   Isabelle  given name   Squirrel  family name   Miss  title              NULL hired date  DATE 2014-01-01  admission date     FROM   dual UNION ALL     SELECT 6 id   Justin  given name   Frog  family name   Master  title             NULL hired date  DATE 2015-04-22  admission date     FROM   dual UNION ALL     SELECT 7 id   Lisa  given name   Owl  family name   Dr  title             DATE 2015-01-01  hired date  NULL admission date     FROM   dual       SELECT   FROM names

[sql] Best way to do multi-row insert in Oracle?

Examples related to sql

Examples related to oracle

Examples related to sql-insert

Examples related to oracle9i