SQL para Principiantes (Parte 8): La declaración INSERT

Base de datos, Curso-SQL, Oracle, SQL

Esta es la octava parte de una serie de artículos que muestran los conceptos básicos de SQL. En este artículo, echamos un vistazo a algunas de las variaciones de la instrucción INSERT.

  • Preparar
  • COMMIT y ROLLBACK
  • INSERT Básico
  • INSERT into View
  • INSERT … SELECT
  • Sub-Consultas Escacalar

Preparar

Puede realizar todas estas consultas en línea de forma gratuita utilizando SQL Fiddle.

Los ejemplos de este artículo requieren que estén presentes las siguientes tablas.

--DROP TABLE employees PURGE;
--DROP TABLE departments PURGE;

CREATE TABLE departments (
  department_id   NUMBER(2) CONSTRAINT departments_pk PRIMARY KEY,
  department_name VARCHAR2(14),
  location        VARCHAR2(13)
);
INSERT INTO departments VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO departments VALUES (20,'RESEARCH','DALLAS');
INSERT INTO departments VALUES (30,'SALES','CHICAGO');
INSERT INTO departments VALUES (40,'OPERATIONS','BOSTON');
COMMIT;

CREATE TABLE employees (
  employee_id   NUMBER(4) CONSTRAINT employees_pk PRIMARY KEY,
  employee_name VARCHAR2(10),
  job           VARCHAR2(9),
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2) CONSTRAINT emp_department_id_fk REFERENCES departments(department_id)
);
INSERT INTO employees VALUES (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO employees VALUES (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO employees VALUES (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO employees VALUES (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO employees VALUES (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO employees VALUES (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO employees VALUES (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO employees VALUES (7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87','dd-mm-rr')-85,3000,NULL,20);
INSERT INTO employees VALUES (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO employees VALUES (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO employees VALUES (7876,'ADAMS','CLERK',7788,to_date('13-JUL-87', 'dd-mm-rr')-51,1100,NULL,20);
INSERT INTO employees VALUES (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO employees VALUES (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO employees VALUES (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
COMMIT;

Estas tablas son una variante de las tablas EMP y DEPT del esquema SCOTT. Verá muchos ejemplos de Oracle en Internet utilizando las tablas del esquema de SCOTT. Puede encontrar las definiciones de la tabla original en el script «$ORACLE_HOME/rdbms/admin/utlsampl.sql».
En este artículo, también vamos a necesitar la siguiente copia de la tabla EMPLOYEES. Observe que hemos hecho obligatorias las columnas EMPLOYEE_NAME y JOB. La columna EMPLOYEE_ID es implícitamente obligatoria, como resultado de ser la clave principal.

-- DROP TABLE employees2 PURGE;

CREATE TABLE employees2 (
  employee_id   NUMBER(4) CONSTRAINT employees2_pk PRIMARY KEY,
  employee_name VARCHAR2(10) NOT NULL,
  job           VARCHAR2(9) NOT NULL,
  manager_id    NUMBER(4),
  hiredate      DATE,
  salary        NUMBER(7,2),
  commission    NUMBER(7,2),
  department_id NUMBER(2)
);

COMMIT y ROLLBACK

Todos los cambios en el lenguaje de manipulación de datos (DML) se realizan como parte de una transacción. No son permanentes hasta que se confirman mediante la declaración COMMIT. Una vez comprometido, la única forma de revertir un cambio es emitir una nueva declaración DML para alterar los datos. Se pueden agrupar varios extractos para formar una sola transacción.
Los comandos del lenguaje de definición de datos (DDL) realizan una confirmación implícita, que también confirma todos los cambios de DML pendientes en la sesión actual.
Si decide que no desea mantener algunos cambios sin confirmar, puede deshacerse de ellos usando la instrucción ROLLBACK. Muchos de los ejemplos de este artículo emitirán declaraciones ROLLBACK después de la prueba, para revertir los datos a su estado original.
NOTA: Algunas herramientas y lenguajes de programación se comprometen automáticamente de forma predeterminada, por lo que emiten automáticamente una declaración COMMIT después de cada declaración DML que procesan. No dejes que esto te engañe haciéndote pensar que es un comportamiento predeterminado. No lo es.

INSERT básica

La versión más simple de la instrucción INSERT no especifica las columnas que se insertarán. Se supone que los valores se especifican en el orden de columna correcto y que no se han omitido valores de columna. Como resultado, esto es peligroso en el código de la aplicación, ya que agregar una columna a la tabla romperá la declaración.

INSERT INTO employees2 VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL> ROLLBACK;

Especificar una lista de columnas es mucho más seguro ya que la declaración no se romperá si se agregan columnas opcionales a la tabla.

INSERT INTO employees2 (employee_id, employee_name, job, manager_id, hiredate, salary, commission, department_id)
VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL> ROLLBACK;

Una lista de columnas explícita también le permite eliminar columnas opcionales de la declaración e intercambiar las posiciones de las columnas en la declaración. En el siguiente ejemplo, las columnas MANAGER_ID, COMMISSION y DEPARTMENT_ID se han eliminado y la columna EMPLOYEE_ID se ha movido al final de la lista de columnas y la lista de valores.

INSERT INTO employees2 (employee_name, job, hiredate, salary, employee_id)
VALUES ('JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800,8888);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL> ROLLBACK;

Recuerde, no se permite perder columnas obligatorias, a menos que tenga valores predeterminados de columna o activadores que las completen. En el siguiente ejemplo se omite la columna JOB, que es una columna obligatoria en la tabla EMPLOYEES2.

INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id)
VALUES (8888,'JONES',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

INSERT INTO employees2 (employee_id, employee_name, manager_id, hiredate, salary, commission, department_id)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("TEST"."EMPLOYEES2"."JOB")

SQL>

INSERT en View

Es posible insertar en la tabla base asociada con una vista. Hay algunas restricciones asociadas con esto, pero están un poco fuera del alcance de un artículo de nivel principiante. En el siguiente ejemplo, creamos una vista simple en la tabla EMPLOYEES2 y luego la insertamos.

CREATE OR REPLACE VIEW employees2_v AS
SELECT * FROM employees2;

INSERT INTO employees2_v VALUES (8888,'JONES','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL> ROLLBACK;

No lo verá muy a menudo, pero también puede insertarlo en vistas en línea. Esto se puede usar para controlar la lista de columnas para la inserción, en lugar de usar una lista de columnas explícita.

INSERT INTO (SELECT employee_id, employee_name, job, hiredate, salary
             FROM   employees2)
VALUES (8888,'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),800);

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK           7902 17-DEC-1980 00:00:00        800                       20

1 row selected.

SQL> ROLLBACK;

INSERT … SELECT

Es posible crear varias filas en una sola instrucción INSERT utilizando la sintaxis INSERT … SELECT. El siguiente ejemplo copia todos los datos de la tabla EMPLOYEES en la tabla EMPLOYEES2.

-- Asegurese de que la table este vacia.
TRUNCATE TABLE employees2;

INSERT INTO employees2
SELECT * FROM employees;

COMMIT;

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20
       7499 ALLEN      SALESMAN        7698 20-FEB-1981 00:00:00       1600        300            30
       7521 WARD       SALESMAN        7698 22-FEB-1981 00:00:00       1250        500            30
       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                       20
       7654 MARTIN     SALESMAN        7698 28-SEP-1981 00:00:00       1250       1400            30
       7698 BLAKE      MANAGER         7839 01-MAY-1981 00:00:00       2850                       30
       7782 CLARK      MANAGER         7839 09-JUN-1981 00:00:00       2450                       10
       7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                       20
       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000                       10
       7844 TURNER     SALESMAN        7698 08-SEP-1981 00:00:00       1500          0            30
       7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                       20
       7900 JAMES      CLERK           7698 03-DEC-1981 00:00:00        950                       30
       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                       20
       7934 MILLER     CLERK           7782 23-JAN-1982 00:00:00       1300                       10

14 rows selected.

SQL>

La lista de columnas de la instrucción INSERT debe coincidir con la lista SELECT de la consulta.

TRUNCATE TABLE employees2;

INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary)
SELECT employee_id, employee_name, job, hiredate, salary
FROM   employees;

COMMIT;

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK                17-DEC-1980 00:00:00        800
       7499 ALLEN      SALESMAN             20-FEB-1981 00:00:00       1600
       7521 WARD       SALESMAN             22-FEB-1981 00:00:00       1250
       7566 JONES      MANAGER              02-APR-1981 00:00:00       2975
       7654 MARTIN     SALESMAN             28-SEP-1981 00:00:00       1250
       7698 BLAKE      MANAGER              01-MAY-1981 00:00:00       2850
       7782 CLARK      MANAGER              09-JUN-1981 00:00:00       2450
       7788 SCOTT      ANALYST              19-APR-1987 00:00:00       3000
       7839 KING       PRESIDENT            17-NOV-1981 00:00:00       5000
       7844 TURNER     SALESMAN             08-SEP-1981 00:00:00       1500
       7876 ADAMS      CLERK                23-MAY-1987 00:00:00       1100
       7900 JAMES      CLERK                03-DEC-1981 00:00:00        950
       7902 FORD       ANALYST              03-DEC-1981 00:00:00       3000
       7934 MILLER     CLERK                23-JAN-1982 00:00:00       1300

14 rows selected.

SQL>

Las reglas normales se aplican a la parte de consulta de la declaración, por lo que puede filtrar los datos o unirse a otras tablas para recopilar los datos.

TRUNCATE TABLE employees2;

INSERT INTO employees2
SELECT *
FROM   employees
WHERE  department_id = 20;

COMMIT;

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       7369 SMITH      CLERK           7902 17-DEC-1980 00:00:00        800                       20
       7566 JONES      MANAGER         7839 02-APR-1981 00:00:00       2975                       20
       7788 SCOTT      ANALYST         7566 19-APR-1987 00:00:00       3000                       20
       7876 ADAMS      CLERK           7788 23-MAY-1987 00:00:00       1100                       20
       7902 FORD       ANALYST         7566 03-DEC-1981 00:00:00       3000                       20

5 rows selected.

SQL>

Sub-Consulta Escalar

Las subconsultas escalares se pueden utilizar para obtener un valor para una instrucción INSERT. En el siguiente ejemplo, la columna SALARIO se completa con una subconsulta escalar, que devuelve el SALARIO máximo de la tabla EMPLEADOS y le agrega 1000.

TRUNCATE TABLE employees2;

INSERT INTO employees2 (employee_id, employee_name, job, hiredate, salary)
VALUES (8888, 'JONES','CLERK',to_date('17-12-1980','dd-mm-yyyy'),(SELECT MAX(salary)+1000 FROM employees));

SELECT *
FROM  employees2
ORDER BY employee_id;

EMPLOYEE_ID EMPLOYEE_N JOB       MANAGER_ID HIREDATE                 SALARY COMMISSION DEPARTMENT_ID
----------- ---------- --------- ---------- -------------------- ---------- ---------- -------------
       8888 JONES      CLERK                17-DEC-1980 00:00:00       6000

1 row selected.

SQL>

Quizás también te puedas interesar:

1 comentario en “SQL para Principiantes (Parte 8): La declaración INSERT”

  1. Pingback: Introducción a SQL - DBandTech.com

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *