Insertar (SQL) - Insert (SQL)

Una instrucción SQL INSERT agrega uno o más registros a cualquier tabla en una base de datos relacional .

Forma básica

Las declaraciones de inserción tienen la siguiente forma:

INSERT INTO tabla ( columna 1 [, columna2 , column3 ...]) VALUES ( valor1 [, valor2 , valor3 ...])

El número de columnas y valores debe ser el mismo. Si no se especifica una columna, se utiliza el valor predeterminado para la columna. Los valores especificados (o implícita) por el INSERT declaración deben satisfacer todas las restricciones aplicables (tales como claves primarias , VER limitaciones , y NOT NULL restricciones). Si se produce un error de sintaxis o si se infringe alguna restricción, la nueva fila no se agrega a la tabla y, en su lugar, se devuelve un error.

Ejemplo:

INSERT INTO phone_book (name, number) VALUES ('John Doe', '555-1212');

También se puede utilizar taquigrafía, aprovechando el orden de las columnas cuando se creó la tabla. No es necesario especificar todas las columnas de la tabla, ya que cualquier otra columna tomará su valor predeterminado o permanecerá nula :

INSERT INTO table VALUES ( valor1 , [ valor2 , ...])

Ejemplo para insertar datos en 2 columnas en la tabla phone_book e ignorar cualquier otra columna que pueda estar después de las 2 primeras en la tabla.

INSERT INTO phone_book VALUES ('John Doe', '555-1212');

Formularios avanzados

Inserciones de varias filas

Una característica de SQL (desde SQL-92 ) es el uso de constructores de valores de fila para insertar varias filas a la vez en una sola declaración SQL:

INSERT INTO tablename (column-a, [column-b, ...])
VALUES ('value-1a', ['value-1b', ...]),
       ('value-2a', ['value-2b', ...]),
       ...

Esta función es compatible con DB2 , SQL Server (desde la versión 10.0 - es decir, 2008), PostgreSQL (desde la versión 8.2), MySQL , SQLite (desde la versión 3.7.11) y H2 .

Ejemplo (asumiendo que 'nombre' y 'número' son las únicas columnas en la tabla 'agenda_de_teléfonos'):

INSERT INTO phone_book VALUES ('John Doe', '555-1212'), ('Peter Doe', '555-2323');

que puede verse como una abreviatura de las dos declaraciones

INSERT INTO phone_book VALUES ('John Doe', '555-1212');
INSERT INTO phone_book VALUES ('Peter Doe', '555-2323');

Tenga en cuenta que las dos declaraciones separadas pueden tener una semántica diferente (especialmente con respecto a los desencadenantes de declaraciones ) y es posible que no proporcionen el mismo rendimiento que una sola inserción de varias filas.

Para insertar varias filas en MS SQL, puede utilizar dicha construcción:

INSERT INTO phone_book
SELECT 'John Doe', '555-1212'
UNION ALL
SELECT 'Peter Doe', '555-2323';

Tenga en cuenta que esta no es una declaración SQL válida según el estándar SQL ( SQL: 2003 ) debido a la cláusula de subselección incompleta.

Para hacer lo mismo en Oracle, use la tabla DUAL , que siempre consta de una sola fila:

INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM DUAL
UNION ALL
SELECT 'Peter Doe','555-2323' FROM DUAL

Una implementación conforme al estándar de esta lógica muestra el siguiente ejemplo, o como se muestra arriba:

INSERT INTO phone_book
SELECT 'John Doe', '555-1212' FROM LATERAL ( VALUES (1) ) AS t(c)
UNION ALL
SELECT 'Peter Doe','555-2323' FROM LATERAL ( VALUES (1) ) AS t(c)

Oracle PL / SQL admite la instrucción INSERT ALL , donde varias instrucciones de inserción terminan con un SELECT :

INSERT ALL
INTO phone_book VALUES ('John Doe', '555-1212')
INTO phone_book VALUES ('Peter Doe', '555-2323')
SELECT * FROM DUAL;

En Firebird, la inserción de varias filas se puede lograr así:

INSERT INTO phone_book (name, number)
SELECT 'John Doe', '555-1212' FROM RDB$DATABASE
UNION ALL
SELECT 'Peter Doe', '555-2323' FROM RDB$DATABASE;

Firebird, sin embargo, restringe la cantidad de filas que se pueden insertar de esta manera, ya que existe un límite en la cantidad de contextos que se pueden usar en una sola consulta.

Copiar filas de otras tablas

Una instrucción INSERT también se puede utilizar para recuperar datos de otras tablas, modificarlos si es necesario e insertarlos directamente en la tabla. Todo esto se realiza en una única sentencia SQL que no implica ningún procesamiento intermedio en la aplicación cliente. Se utiliza una subselección en lugar de la cláusula VALUES . La subselección puede contener combinaciones, llamadas a funciones e incluso puede consultar la misma tabla en la que se insertan los datos. Lógicamente, la selección se evalúa antes de que se inicie la operación de inserción real. A continuación se ofrece un ejemplo.

INSERT INTO phone_book2
SELECT *
FROM   phone_book
WHERE  name IN ('John Doe', 'Peter Doe')

Se necesita una variación cuando algunos de los datos de la tabla de origen se insertan en la nueva tabla, pero no el registro completo. (O cuando los esquemas de las tablas no son los mismos).

INSERT INTO phone_book2 (name, number)
SELECT name, number
FROM   phone_book
WHERE  name IN ('John Doe', 'Peter Doe')

La instrucción SELECT produce una tabla (temporal) y el esquema de esa tabla temporal debe coincidir con el esquema de la tabla donde se insertan los datos.

Valores predeterminados

Es posible insertar una nueva fila sin especificar ningún dato, utilizando valores predeterminados para todas las columnas. Sin embargo, algunas bases de datos rechazan la declaración si no se proporcionan datos, como Microsoft SQL Server, y en este caso se puede utilizar la palabra clave DEFAULT .

INSERT INTO phone_book
VALUES ( DEFAULT )

A veces, las bases de datos también admiten una sintaxis alternativa para esto; por ejemplo, MySQL permite omitir la palabra clave DEFAULT , y T-SQL puede usar VALORES POR DEFECTO en lugar de VALORES (POR DEFECTO) . La palabra clave DEFAULT también se puede usar en la inserción normal para llenar explícitamente una columna usando el valor predeterminado de esa columna:

INSERT INTO phone_book VALUES ( DEFAULT, '555-1212' )

Lo que sucede cuando una columna no especifica un valor predeterminado depende de la base de datos. Por ejemplo, MySQL y SQLite completarán con un valor en blanco (excepto en modo estricto), mientras que muchas otras bases de datos rechazarán la declaración.

Recuperando la llave

Los diseñadores de bases de datos que utilizan una clave sustituta como clave principal para cada tabla se encontrarán con el escenario ocasional en el que necesitan recuperar automáticamente la clave principal generada por la base de datos de una instrucción SQL INSERT para su uso en otras declaraciones SQL. La mayoría de los sistemas no permiten que las instrucciones SQL INSERT devuelvan datos de fila. Por lo tanto, es necesario implementar una solución alternativa en tales escenarios. Las implementaciones comunes incluyen:

• Usando un procedimiento almacenado específico de la base de datos que genera la clave sustituta, realiza la operación INSERT y finalmente devuelve la clave generada. Por ejemplo, en Microsoft SQL Server, la clave se recupera mediante la función especial SCOPE_IDENTITY () , mientras que en SQLite la función se denomina last_insert_rowid () .
• Usar una instrucción SELECT específica de la base de datos en una tabla temporal que contiene las últimas filas insertadas. DB2 implementa esta característica de la siguiente manera:

  SELECT *
  FROM NEW TABLE (
      INSERT INTO phone_book
      VALUES ( 'Peter Doe','555-2323' )
  ) AS t
  

  • DB2 para z / OS implementa esta característica de la siguiente manera.

    SELECT EMPNO, HIRETYPE, HIREDATE
    FROM FINAL TABLE (
        INSERT INTO EMPSAMP (NAME, SALARY, DEPTNO, LEVEL)
        VALUES('Mary Smith', 35000.00, 11, 'Associate')
    );
    

• Usar una instrucción SELECT después de la instrucción INSERT con una función específica de la base de datos que devuelve la clave primaria generada para la fila insertada más recientemente. Por ejemplo, LAST_INSERT_ID () para MySQL .
• Usar una combinación única de elementos del SQL INSERT original en una instrucción SELECT posterior .
• Usar un GUID en la instrucción SQL INSERT y recuperarlo en una instrucción SELECT .
• Usando la cláusula OUTPUT en la instrucción SQL INSERT para MS-SQL Server 2005 y MS-SQL Server 2008.
• Usando una instrucción INSERT con la cláusula RETURNING para Oracle .

  INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
  RETURNING phone_book_id INTO v_pb_id
  

• Usando una instrucción INSERT con la cláusula RETURNING para PostgreSQL (desde 8.2). La lista devuelta es idéntica al resultado de INSERT .
  • Firebird tiene la misma sintaxis en las declaraciones del lenguaje de modificación de datos (DSQL); la declaración puede agregar como máximo una fila. En procedimientos almacenados, disparadores y bloques de ejecución (PSQL) se utiliza la sintaxis de Oracle antes mencionada.

    INSERT INTO phone_book VALUES ( 'Peter Doe','555-2323' )
    RETURNING phone_book_id
    

• El uso de la función IDENTITY () en H2 devuelve la última identidad insertada.

  SELECT IDENTITY();
  

Disparadores

Si se definen desencadenadores en la tabla en la que opera la instrucción INSERT , esos desencadenantes se evalúan en el contexto de la operación. Los disparadores ANTES DE INSERTAR permiten modificar los valores que se insertarán en la tabla. Los disparadores AFTER INSERT ya no pueden modificar los datos, pero se pueden usar para iniciar acciones en otras tablas, por ejemplo, para implementar un mecanismo de auditoría.

Referencias

enlaces externos

• Declaración de Oracle SQL INSERT (referencia del lenguaje SQL de la base de datos de Oracle, versión 11g 2 (11.2) en oracle.com)
• Microsoft Access Append Query Examples y SQL INSERT Query Syntax
• Declaración INSERT de MySQL (Manual de referencia de MySQL 5.5)