El TecnoBaúl de Kiquenet

Kiquenet boring stories

Posts Tagged ‘SQL’

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.

Posted in SQL | Etiquetado: | Leave a Comment »

SQL SELECT TOP N equivalent in ORACLE and MySQL

Posted by kiquenet en 26 agosto 2009

SQL SELECT TOP N equivalent in ORACLE and MySQL

SQL Server:

SELECT TOP 10 product, descr, email
FROM products

ORACLE:

SELECT product, descr, email
FROM products
WHERE ROWNUM <= 10

MySQL:

SELECT product, descr, email
FROM products
LIMIT 10

Posted in SQL | Etiquetado: | Leave a Comment »