Oracle multi-row insert statement

Question

Kind of idiotic that Oracle doesn't support this type of insert, how would I do this in Oracle?

INSERT INTO WEBSITE_GATEKEEPER_STATE (PRG_CODE, STATE) VALUES("BPA", "AL"), ("BPA", "AK"), ("BPA", "AS"), ("BPA", "AZ"), ("BPA", "AR"), ("BPA", "AF"), 
 ("BPA", "AA"), ("BPA", "AC"), ("BPA", "AE"), ("BPA", "AM"), ("BPA", "AP"), ("BPA", "CA"), ("BPA", "CO"), ("BPA", "CT"), ("BPA", "DE"), ("BPA", "DC"), 
 ("BPA", "FM"), ("BPA", "FL"), ("BPA", "GA"), ("BPA", "GU"), ("BPA", "HI"), ("BPA", "ID"), ("BPA", "IL"), ("BPA", "IN"), ("BPA", "IA"), ("BPA", "KS"),
 ("BPA", "KY"), ("BPA", "LA"), ("BPA", "ME"), ("BPA", "MH"), ("BPA", "MD"), ("BPA", "MA"), ("BPA", "MI"), ("BPA", "MN"), ("BPA", "MS"), ("BPA", "MO"),
 ("BPA", "MT"), ("BPA", "NE"), ("BPA", "NV"), ("BPA", "NH"), ("BPA", "NJ"), ("BPA", "NM"), ("BPA", "NY"), ("BPA", "NC"), ("BPA", "ND"), ("BPA", "MP"),
 ("BPA", "OH"), ("BPA", "OK"), ("BPA", "OR"), ("BPA", "PW"), ("BPA", "PA"), ("BPA", "PR"), ("BPA", "RI"), ("BPA", "SC"), ("BPA", "SD"), ("BPA", "TN"),
 ("BPA", "TX"), ("BPA", "UT"), ("BPA", "VT"), ("BPA", "VI"), ("BPA", "VA"), ("BPA", "WA"), ("BPA", "WV"), ("BPA", "WI"), ("BPA", "WY");

Like this perhaps: techonthenet.com/oracle/questions/insert_rows.php — Klaus Byskov Pedersen
– Klaus Byskov Pedersen, Commented Feb 20, 2012 at 16:55
why didn't you post as answer? It worked so I will select as answer if you post as one. — MB34
– MB34, Commented Feb 20, 2012 at 17:57
A duplicate: stackoverflow.com/q/4152037. Note that there is nothing special to PL/SQL here - it's just Oracle SQL that can be executed also in PL/SQL context. — user272735
– user272735, Commented Feb 20, 2012 at 18:10
possible duplicate of inserting multiple rows with one insert command — Jon Heller
– Jon Heller, Commented Feb 20, 2012 at 19:48
@user272735 - No it is not a duplicate. Oracle is idiotic that you can't use std SQL92 code to do this. WTF is DUAL anyway, kind of stupid if you ask me. — MB34
– MB34, Commented Feb 20, 2012 at 22:54

Klaus Byskov Pedersen · Accepted Answer · 2012-02-20 18:20:46Z

3

Like this perhaps: http://www.techonthenet.com/oracle/questions/insert_rows.php

answered Feb 20, 2012 at 18:20

Klaus Byskov Pedersen

122k31 gold badges192 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user330315 Over a year ago

What's the advantage over writing multiple INSERTs statements (one for each row)?

jim mcnamara Over a year ago

A small amount of overhead. Cursor sharing does not play well with SQL that has no bind variables, so each iteration of something like that one line insert statement requires a re-parse. Really, it is not that big a deal unless you are loading large numbers of rows, which the OP does not seem to be doing.

user330315 Over a year ago

For that example the hard parses would not matter at all. If a large number of rows should be loaded a prepared statement or even SQL*Loader is a much better choice

Basic Over a year ago

Can you please add an example to the answer, not just link externally - it makes it easier for future visitors and prevents link rot devaluing your answer...

APC · Accepted Answer · 2012-02-20 22:06:53Z

I suppose it depends on one's definition of "idiotic". This thing is the sort of data loading we ought to be doing only occasionally. So it isn't really a big hardship to use cut'n'paste to produce a script which fits the available syntax.

Or grep. I used regex in an editor to turn your code into viable PL/SQL code. Check it out:

declare
    strs dbms_debug_vc2coll;
begin
     strs := dbms_debug_vc2coll ( 'AL', 'AK', 'AS', 'AZ', 'AR', 'AF', 
         'AA', 'AC', 'AE', 'AM', 'AP', 'CA', 'CO', 'CT', 'DE', 'DC', 
         'FM', 'FL', 'GA', 'GU', 'HI', 'ID', 'IL', 'IN', 'IA', 'KS',
         'KY', 'LA', 'ME', 'MH', 'MD', 'MA', 'MI', 'MN', 'MS', 'MO',
         'MT', 'NE', 'NV', 'NH', 'NJ', 'NM', 'NY', 'NC', 'ND', 'MP',
         'OH', 'OK', 'OR', 'PW', 'PA', 'PR', 'RI', 'SC', 'SD', 'TN',
         'TX', 'UT', 'VT', 'VI', 'VA', 'WA', 'WV', 'WI', 'WY');


     INSERT INTO WEBSITE_GATEKEEPER_STATE (PRG_CODE, STATE) 
     select 'PA', column_value
     from table ( select * from strs );

 end;
 /

S.Bozzoni · Accepted Answer · 2016-05-15 14:29:43Z

1

INSERT ALL
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
  INTO mytable (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

or ...

insert into mytable (column1, column2, .. columnn)
          select value1, value2 ... valuen from dual
union all select value1, value2 ... valuen from dual

answered May 15, 2016 at 14:29

S.Bozzoni

1,00210 silver badges19 bronze badges

Comments

Rahul Parit · Accepted Answer · 2015-04-14 12:38:33Z

0

you can use &variable_name to enter the values at run time instead of writing every new statement.

For example -
 INSERT INTO WEBSITE_GATEKEEPER_STATE (PRG_CODE, STATE) 
 VALUES(&PRG_CODE, &STATE);

answered Apr 14, 2015 at 12:38

Rahul Parit

3413 silver badges11 bronze badges

Comments

aljassi · Accepted Answer · 2017-11-06 15:39:35Z

0

by this way

DECLARE

var_sql clob;
var_bpa varchar2(3):='BPA';

BEGIN

var_sql := 'insert all ';
var_sql :=var_sql||' into website_gatekeeper_state (prg_code, state) 
values('''||var_bpa||''', ''AL'')';
var_sql :=var_sql||' into website_gatekeeper_state (prg_code, state) 
values('''||var_bpa||''', ''AF'')';
var_sql :=var_sql||' into website_gatekeeper_state (prg_code, state) 
values('''||var_bpa||''', ''AM'')';
var_sql :=var_sql||' into website_gatekeeper_state (prg_code, state) 
values('''||var_bpa||''', ''AP'')';
var_sql :=var_sql||' into website_gatekeeper_state (prg_code, state) 
values('''||var_bpa||''', ''DC'')';
var_sql :=var_sql||' select 1 from dual ';   

EXECUTE IMMEDIATE var_sql; 

END;

edited Nov 6, 2017 at 15:39

answered Apr 9, 2015 at 8:38

aljassi

2462 silver badges10 bronze badges

Collectives™ on Stack Overflow

Oracle multi-row insert statement

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

4 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related