INSERT ALL without a select statement?

Question

Hope someone can help me. I want to add many records into a table from a statement I build. For example:

ProjectSQL = "INSERT ALL "
For each Proj in split(Request.Form("ShowProjects"))
  ProjectSQL = ProjectSQL & "  INTO proj_tbl (proj_id) VALUES ('" & Proj & "') "
Next
db.Execute ProjectSQL

The SQL is:
INSERT ALL 
  INTO proj_tbl (proj_id) VALUES ('45) 
  INTO proj_tbl (proj_id) VALUES ('56) 
  INTO proj_tbl (proj_id) VALUES ('92)

Oracle SQL wants a Select statement at the end.

I don't want to hammer the database with each loop because there could be many records to add (and the dba will cut off my balls).

Can someone tell me how to do this?

Thanks.

If you have to insert many rows, you have to insert many rows. There is no way to avoid it. I don't know which programming language you are using but e.g. Java/JDBC and other interface support batching of statements, maybe that is available in your programming language as well. — , Jan 20 '14 at 20:24

score 4 · Answer 1 · answered Jan 20 '14 at 20:34

Assuming that proj_id is a number, you could build a SQL statement like this that does a meaningless query from the dual table

INSERT ALL 
  INTO proj_tbl (proj_id) VALUES (45) 
  INTO proj_tbl (proj_id) VALUES (56) 
  INTO proj_tbl (proj_id) VALUES (92) 
  SELECT 1 FROM dual

score 1 · Answer 2 · answered Jan 20 '14 at 22:36

1

Why not just build the statement as:

INSERT INTO proj_tbl(proj_id)
    select 45 from dual union all
    select 56 from dual union all
    select 92;

answered Jan 20 '14 at 22:36

Gordon Linoff

1,242,037
58
646
786

INSERT ALL without a select statement?

2 Answers2