INSERT Multiple Rows - MySQL to Oracle Migration

In MySQL, you can insert multiple rows using a single INSERT statement:

MySQL:

  -- Sample table
  CREATE TABLE cities
  (
      name VARCHAR(70),
      state CHAR(2)
  );
 
  -- Insert multiple rows with single statement
  INSERT INTO cities (name, state) VALUES
    ('San Francisco', 'CA'),
    ('New York', 'NY'),
    ('Los Angeles', 'CA');

Oracle does not support this syntax, but you can use multiple separate INSERT statements, single INSERT statement with SELECT UNION ALL, or single INSERT ALL statement.

Using multiple INSERT statements in Oracle:

Oracle:

  -- Sample table
  CREATE TABLE cities
  (
      name VARCHAR2(70),
      state CHAR(2)
  );
 
  -- Using separate INSERT statement for each row
  INSERT INTO cities (name, state) VALUES ('San Francisco', 'CA');
  INSERT INTO cities (name, state) VALUES ('New York', 'NY');
  INSERT INTO cities (name, state) VALUES ('Los Angeles', 'CA');

You can also use a single INSERT statement and SELECT UNION ALL to build a subquery returning multiple rows:

  -- Using single INSERT statement
  INSERT INTO cities (name, state)
   SELECT 'San Francisco', 'CA' FROM dual UNION ALL
   SELECT 'New York', 'NY' FROM dual UNION ALL
   SELECT 'Los Angeles', 'CA' FROM dual;

One more way is to use INSERT ALL statement as follows:

  -- Using single INSERT ALL statement
  INSERT ALL 
     INTO cities (name, state) VALUES ('San Francisco', 'CA')
     INTO cities (name, state) VALUES ('New York', 'NY')
     INTO cities (name, state) VALUES ('Los Angeles', 'CA')
   SELECT 1 FROM dual;

More Information

About SQLines

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko - March 2013.

You could leave a comment if you were logged in.