Bulk Insert with large static data in Oracle

Question

I have a simple table with one column of numbers. I want to load about 3000 numbers in it. I want to do that in memory, without using SQL*Loader. I tried

INSERT ALL
   INTO t_table (code) VALUES (n1)
   INTO t_table (code) VALUES (n2)
   ...
   ...
   INTO t_table (code) VALUES (n3000)
SELECT * FROM dual

But I fails at 1000 values. What should I do ? Is SQL*Loader the only way ? Can I do LOAD with SQL only ?

What is this `load` you're talking about? SQL*Loader? Is this a one off or something that's going to have to happen regularly? If regularly will the file structure always be the same> — Ben, Aug 09 '12 at 07:46
yes, I realize that it is the SQL*Loader that I am talking about. so I don4t want to use it. I edited the auestion — Baptiste Pernet, Aug 09 '12 at 07:48

APC · Accepted Answer · 2012-08-09T11:14:48.263

Presumably you have an initial value of n. If so, this code will populate code with values n to n+2999 :

insert into t_table (code)
select (&N + level ) - 1
from dual
connect by level <=3000

This query uses a SQL*Plus substitution variable to post the initial value of n. Other clients will need to pass the value in a different way.

"Assume that I am in c++ with a stl::vector, what query should I write ?"

So when you wrote n3000 what you really meant was n(3000). It's easy enough to use an array in SQL. This example uses one of Oracle's pre-defined collections, a table of type NUMBER:

declare 
    ids system.number_tbl_type; 
begin
    insert into t_table (code)
    select column_value
    from table ( select ids from dual )
    ; 
end;

As for mapping your C++ vector to Oracle types, that's a different question (and one which I can't answer).

I don't understand your answer. Assume that I am in c++ with a stl::vector, what query should I write ? — Baptiste Pernet, Aug 09 '12 at 08:37

Bulk Insert with large static data in Oracle

1 Answers1