Multiple SQL inserts: Oracle, Mysql, Mssql
Found a useful technique for inserting multiple rows into Oracle, MYSQL and MS SQL databases.
Mysql supports this:INSERT INTO tblName (column1,column2)
VALUES
('Value1','Fernando'),
('Value2','Carlos'),
('Value3','Vincent')
Oracle does not, but can be achieved with:
VALUES
('Value1','Fernando'),
('Value2','Carlos'),
('Value3','Vincent')
INSERT INTO tblName (column1,column2)
select ('Value1','Fernando') from dual
union all
select ('Value2','Carlos') from dual
union all
select ('Value3','Vincent') from dual
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')
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.
