Oracle: Bulk insert using an array

You really don’t want to generate an insert for each row you want to insert, this will be slow to create, slow to transmit, slow to parse and slow to process. I also don’t like creating CSVs and importing these via SQL Plus. I’m an application developer, so I want to do bulk operations via ODP.NET.

Instead I use arrays, they truly are wonderful and often overlooked.
A syntax like so is really useful to remember:
DECLARE iVal INT;
BEGIN
FOR iVal IN 1..200 LOOP
INSERT INTO [TABLE] (column1, column2) VALUES (:array1[iVal], :array2[iVal]);
END LOOP;
END;

where array1 and array2 are PLSQLAssociativeArray arrays.

See also Oracle: Using PLSQLAssociativeArray with ODP.NET

Advertisements

2 responses to “Oracle: Bulk insert using an array

  1. Pingback: Oracle: Using PLSQLAssociativeArray with ODP.NET | Tim's cycling blog

  2. Pingback: SQL Server: Bulk updates, bulk inserts and table valued parameters for arrays | Tim's cycling blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s