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;
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.