Multiple SQL inserts: Oracle, Mysql, Mssql

I’ve found a useful technique for inserting multiple rows into Oracle, MYSQL and MS SQL databases. Using these examples will help increase the speed and help avoid database timeouts.

Mysql supports this:

INSERT INTO tblName (column1,column2)
VALUES
('Value1','Fernando'),
('Value2','Carlos'),
('Value3','Vincent')

Oracle does not, but can be achieved with:

INSERT INTO tblName (column1,column2)
select 'Value1','Fernando' from dual
union all
select 'Value2','Carlos' from dual
union all
select 'Value3','Vincent' from dual

DUAL is a table which is created by oracle along with the data dictionary. It consists of exactly one column whose name is dummy and one record.

In MS SQL the union all technique also works:

INSERT INTO [TABLE] (column1,column2)
select 'Value1','Fernando'
UNION ALL
select 'Value2','Carlos'
UNION ALL
select 'Value3','Vincent'

This technique is probably a lot faster than using multiple inserts in coldfusion and certainly helps reduce the round-trips that would need to be done.

Related posts:

1. SQL SELECT TOP N equivalent in ORACLE and MySQL Something I needed today… I wish this was standarized, but...
2. Eclipse Oracle Plugin jOra is an Eclipse-Plugin for Oracle Developers and Oracle Admins....
3. SQL – The case for CASE The Transact SQL Case statement is a particularly useful piece...
4. SQL – getting table info Sometimes I’ve had the need to use SQL to go...