El TecnoBaúl de Kiquenet

Kiquenet boring stories

Multiple SQL inserts: Oracle, Mysql, Mssql

Posted by kiquenet en 27 agosto 2009

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.

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

 
A %d blogueros les gusta esto: