Multiple Inserts in Oracle (example using PHP)

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();
    }
 }
}

Advertisements

Autoincrement in Oracle DB mittels Sequence und Trigger

CREATE SEQUENCE [tablename]_SEQ

START WITH 1
MAXVALUE 9999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;

CREATE OR REPLACE TRIGGER [tablename]_trigger

BEFORE INSERT ON [tablename]

FOR EACH ROW

BEGIN

SELECT [tablename]_SEQ.NEXTVAL INTO :NEW.[primarykey] FROM DUAL;

END;