INSERT INTO (Databricks SQL)

Inserta nuevas filas en una tabla. Especifique las filas insertadas por expresiones de valor o el resultado de una consulta.

Syntax

INSERT INTO [ TABLE ] table_name [ partition_spec ]
    { VALUES { ( { value | NULL } [, ...] ) } [, ...] ] |
      query }

partition_spec
  PARTITION ( { partition_col_name1  = partition_col_val1 } [, ...] )

Nota

Cuando se admite la evolución y el cumplimiento del INSERT INTO esquema de tabla delta. Si el tipo de datos de una columna no se puede convertir de forma segura al tipo de datos de una tabla Delta, se produce una excepción en tiempo de ejecución. Si la evolución del esquema está habilitada, pueden existir nuevas columnas como últimas columnas del esquema (o columnas anidadas) para que el esquema evolucione.

Parámetros

  • Table_name

    Identifica la tabla en la que se va a insertar. El nombre no debe incluir una especificación temporal.

  • partition_spec

    Parámetro opcional que especifica una lista separada por comas de pares clave-valor para particiones.

  • VALUES ( { value | NULL } [ , ... ] ) [ , ( ... ) ]

    Valores que se van a insertar. Valor especificado explícitamente o NULL . Use una coma para separar cada valor de la cláusula . Puede especificar más de un conjunto de valores para insertar varias filas.

  • Consulta

    Consulta que genera las filas que se insertarán. Uno de los siguientes formatos:

    • Una SELECT instrucción
    • Una TABLE instrucción
    • Una FROM instrucción

Ejemplos

En esta sección:

Inserción de una sola fila mediante una VALUES cláusula

CREATE TABLE students (name VARCHAR(64), address VARCHAR(64), student_id INT)
  PARTITIONED BY (student_id);

INSERT INTO students VALUES
    ('Amy Smith', '123 Park Ave, San Jose', 111111);

SELECT * FROM students;
+---------+---------------------+----------+
|     name|              address|student_id|
+---------+---------------------+----------+
|Amy Smith|123 Park Ave,San Jose|    111111|
+---------+---------------------+----------+

Inserción de varias filas mediante una VALUES cláusula

INSERT INTO students VALUES
    ('Bob Brown', '456 Taylor St, Cupertino', 222222),
    ('Cathy Johnson', '789 Race Ave, Palo Alto', 333333);

SELECT * FROM students;
+-------------+------------------------+----------+
|         name|                 address|student_id|
+-------------+------------------------+----------+
|    Amy Smith|  123 Park Ave, San Jose|    111111|
+-------------+------------------------+----------+
|    Bob Brown|456 Taylor St, Cupertino|    222222|
+-------------+------------------------+----------+
|Cathy Johnson| 789 Race Ave, Palo Alto|    333333|
+--------------+-----------------------+----------+

Inserción mediante una SELECT instrucción

-- Assuming the persons table has already been created and populated.
SELECT * FROM persons;
+-------------+-------------------------+---------+
|         name|                  address|      ssn|
+-------------+-------------------------+---------+
|Dora Williams|134 Forest Ave, Melo Park|123456789|
+-------------+-------------------------+---------+
|  Eddie Davis|  245 Market St, Milpitas|345678901|
+-------------+-------------------------+---------+

INSERT INTO students PARTITION (student_id = 444444)
    SELECT name, address FROM persons WHERE name = "Dora Williams";

SELECT * FROM students;
+-------------+-------------------------+----------+
|         name|                  address|student_id|
+-------------+-------------------------+----------+
|    Amy Smith|   123 Park Ave, San Jose|    111111|
+-------------+-------------------------+----------+
|    Bob Brown| 456 Taylor St, Cupertino|    222222|
+-------------+-------------------------+----------+
|Cathy Johnson|  789 Race Ave, Palo Alto|    333333|
+-------------+-------------------------+----------+
|Dora Williams|134 Forest Ave, Melo Park|    444444|
+-------------+-------------------------+----------+

Inserción mediante una TABLE instrucción

-- Assuming the visiting_students table has already been created and populated.
SELECT * FROM visiting_students;
+-------------+---------------------+----------+
|         name|              address|student_id|
+-------------+---------------------+----------+
|Fleur Laurent|345 Copper St, London|    777777|
+-------------+---------------------+----------+
|Gordon Martin| 779 Lake Ave, Oxford|    888888|
+-------------+---------------------+----------+

INSERT INTO students TABLE visiting_students;

SELECT * FROM students;
+-------------+-------------------------+----------+
|         name|                  address|student_id|
+-------------+-------------------------+----------+
|    Amy Smith|    123 Park Ave,San Jose|    111111|
+-------------+-------------------------+----------+
|    Bob Brown| 456 Taylor St, Cupertino|    222222|
+-------------+-------------------------+----------+
|Cathy Johnson|  789 Race Ave, Palo Alto|    333333|
+-------------+-------------------------+----------+
|Dora Williams|134 Forest Ave, Melo Park|    444444|
+-------------+-------------------------+----------+
|Fleur Laurent|    345 Copper St, London|    777777|
+-------------+-------------------------+----------+
|Gordon Martin|     779 Lake Ave, Oxford|    888888|
+-------------+-------------------------+----------+

Inserción mediante una FROM instrucción

-- Assuming the applicants table has already been created and populated.
SELECT * FROM applicants;
+-----------+--------------------------+----------+---------+
|       name|                   address|student_id|qualified|
+-----------+--------------------------+----------+---------+
|Helen Davis| 469 Mission St, San Diego|    999999|     true|
+-----------+--------------------------+----------+---------+
|   Ivy King|367 Leigh Ave, Santa Clara|    101010|    false|
+-----------+--------------------------+----------+---------+
| Jason Wang|     908 Bird St, Saratoga|    121212|     true|
+-----------+--------------------------+----------+---------+

INSERT INTO students
     FROM applicants SELECT name, address, id applicants WHERE qualified = true;

SELECT * FROM students;
+-------------+-------------------------+----------+
|         name|                  address|student_id|
+-------------+-------------------------+----------+
|    Amy Smith|   123 Park Ave, San Jose|    111111|
+-------------+-------------------------+----------+
|    Bob Brown| 456 Taylor St, Cupertino|    222222|
+-------------+-------------------------+----------+
|Cathy Johnson|  789 Race Ave, Palo Alto|    333333|
+-------------+-------------------------+----------+
|Dora Williams|134 Forest Ave, Melo Park|    444444|
+-------------+-------------------------+----------+
|Fleur Laurent|    345 Copper St, London|    777777|
+-------------+-------------------------+----------+
|Gordon Martin|     779 Lake Ave, Oxford|    888888|
+-------------+-------------------------+----------+
|  Helen Davis|469 Mission St, San Diego|    999999|
+-------------+-------------------------+----------+
|   Jason Wang|    908 Bird St, Saratoga|    121212|
+-------------+-------------------------+----------+

Inserción en un directorio

CREATE TABLE students (name VARCHAR(64), address VARCHAR(64), student_id INT)
    PARTITIONED BY (student_id)
    LOCATION "/mnt/user1/students";

INSERT INTO delta.`/mnt/user1/students` VALUES
    ('Amy Smith', '123 Park Ave, San Jose', 111111);
SELECT * FROM students;
+-------------+-------------------------+----------+
|         name|                  address|student_id|
+-------------+-------------------------+----------+
|    Amy Smith|   123 Park Ave, San Jose|    111111|
+-------------+-------------------------+----------+