Bulk Insert in Oracle table 10g without stored procedure/avoid loop

Question

I have a datatable of 3 columns ID (int), Name (string), Status (string) and of 1000 rows .. and a table with 4 columns seq (generated from SEQR.NEXTVAL), ID, Name, Status.

I want to insert all 1000 rows in a single query. I mean avoiding 1000 times database hit using loops for performance issue.

Constraints are:

I am using Oracle 10g
stored procedures, cursors not allowed
To temporary table creation allowed as DBA is from client side so I can't create DB tables from my side

Any suggestion with proper code? I am using Oracle client and DbCommand object

David Aldridge · Accepted Answer · 2013-06-26 18:52:23Z

2

So just use a single SQL statement, right?

insert into table_a (seq_col, id, name, status)
select seqr.nextval, id, name, status
from table_b;

answered Jun 26, 2013 at 18:52

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

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

1 Comment

Rahul Chowdhury Over a year ago

But here one table is datatable another is database table

Jafar Kofahi · Accepted Answer · 2013-06-26 20:20:15Z

0

If I understood your question correctly, you can use a Select statement in your insert statement:

Insert into table2
  Select SEQR.NEXTVAL, tab1.*
    from table1 as tab1

edited Jun 26, 2013 at 20:20

answered Jun 26, 2013 at 18:53

Jafar Kofahi

7637 silver badges23 bronze badges

1 Comment

Jafar Kofahi Over a year ago

@RahulChowdhury So you want to write a query from .Net side and insert a 1000 rows in one trip to the server. I am now aware of a way to do that. Why cant you use stored procedures?

LDS · Accepted Answer · 2014-10-31 08:11:35Z

0

Use Oracle array DML. YOu just fill the column arrays in a loop from your code, and the send everything as a single INSERT statement. Anyway IMHO 1000 INSERTs with a properly written query using bind variables won't hit performance issues. You'll get them if you don't use bind variables and commit each time...

answered Oct 31, 2014 at 8:11

LDS

3461 silver badge4 bronze badges

Collectives™ on Stack Overflow

Bulk Insert in Oracle table 10g without stored procedure/avoid loop

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related