El TecnoBaúl de Kiquenet

Kiquenet boring stories

Archive for 27 agosto 2009

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 »

Bienvenidos al TecnoBaúl de Kiquenet

Posted by kiquenet en 18 agosto 2009

Bienvenidos a El Tecno-Baúl de Kiquenet en WordPress.

Iniciamos una aventura bloguera con WordPress, para centrarnos en tecnología, desarrollo, etcétera.

Nuestro hermano en la red: http://kiquenet.blogspot.com/

Nos vemos en los bares.

Posted in Uncategorized | Etiquetado: | Leave a Comment »