Everybody knows how to make multiple inserts into MySQL:
INSERT INTO [table_name] ([col_1], [col_2]) VALUES ([val_1_1], [val_1_2]), ([val_2_1], [val_2_2]), ([val_3_1], [val_3_2])
That´s easy! But multiple inserts to an Oracle DB don´t work like that… here are two ways of doing the same job with Oracle:
insert all into [table_name] ([col1], [col2]) values ([val1_1], [val1_2]) into [table_name] ([col1], [col2]) values ([val2_1], [val2_2]) into [table_name] ([col1], [col2]) values ([val3_1], [val3_2]) select 1 from dual
or alternatively
insert into [table_name] ([col1], [col2]) select [val1_1], [val1_2] from dual union all select [val2_1], [val2_2] from dual union all select [val3_1], [val3_2] from dual
This is useful if you need to insert a lot of rows into a table – which usually occurs in a job like data import from other sources rather than as a result of user interaction. Running a PHP script for example to insert 50.000 rows into a table would fire 50.000 inserts if you don´t use multiple inserts. This is slow and can also cause you to run into memory problems.
Here is a simple example of how to do this in PHP:
class MyImportTest extends MyDatabaseModel { protected static $_parallelImports = 50; protected static $_insertCache = array(); public static function insertImportDataIntoDb($inputData) { $added = 0; foreach ($inputdata as $inputArray) { $added += self::_addCaseToInsertCache($inputArray['col1'], $inputArray['col2']); if ($added % self::$_parallelInserts == 0) { self::_flushInsertCache(); } } if ($added % self::$_parallelInserts != 0) { self::_flushInsertCache(); } } function addCaseToInsertCache($col1, $col2) { // maybe do validity checks here and return 0 if an element is not added self::$_insertCache[] = array($col1, $col2); return 1; } function flushInsertCache() { $db = self::getDb(); $sql = 'INSERT INTO "TABLE_NAME" ("COL1", "COL2")'; $values = ''; foreach (self::$_insertCache as $insert) { $values .= (empty($values)) ? '' : ' UNION ALL '; $values .= 'SELECT ' . $insert[0] . ', ' . $insert[1] . ', ' . $insert[5] . ' FROM DUAL'; } // if using string values, then don't forget to use quotes! $db->query($sql . $values); self::$_insertCache = array(); } } }