Insertar datos en Transact SQL

Inserción individual de filas.

Para realizar la insercción individual de filas SQL posee la instrucción INSERT INTO.La insercción individual de filas es la que más comunmente utilizaremos. Su sintaxis es la siguiente:

INSERT INTO <nombre_tabla> [(<campo1>[,<campo2>,...])] values (<valor1>,<valor2>,...);

El siguiente ejemplo muestra la inserción de un registro en la tabla PRECIOS.

INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) VALUES (10, getdate(),getdate()+30, 1)

Insertción múltiple de filas.

También es posible insertar en una tabla el resultado de una consulta SELECT. De este modo se insertarán tantas filas como haya devuelto la consulta SELECT.

El siguiente ejemplo muestra la inserción multiple de filas.

INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) SELECT PRECIO_UNIDAD, getdate(), getdate() + 30, CO_PRODUCTO FROM DETALLE_PEDIDO

Inserción de valores por defecto.

También podemos forzar a que la insercción se realice con los datos por defecto establecidos para la tabla (o null si no tienen valores por defecto).

INSERT INTO PRECIOS DEFAULT VALUES

En SQL Sever podemos marcar un campo de una tabla como autonumérico (identity), cuando insertamos un registro en dicha tabla el valor del campo se genera automaticamente. Para recuperar el valor generado disponemos de varios métodos:

• Utilizar la funcion @@identity, que devuelve el último valor identidad insertado por la transaccion:

DECLARE @Codigo int INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) VALUES (10, getdate(),getdate()+30, 1) set @Codigo = @@Identity PRINT @Codigo

El uso de @@Identity no siempre es válido, ya que al devolver el úlitmo valor identidad insertado por la transacción, no nos garantiza que el valor haya sido insertado en la tabla que nos interesa (por ejemplo la tabla podría tener un trigger que insertara datos en otra tabla con campos identidad).

• En este tipo de escenarios debemos utilizar la función, SCOPE_IDENTITY.

DECLARE @Codigo int INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) VALUES (10, getdate(),getdate()+30, 1) SET @Codigo = SCOPE_IDENTITY() PRINT @Codigo

Clausula OUTPUT

A partir de la version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción UPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los datos "despues" del cambio.

INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE.

DECLARE @FILAS_INSERTADAS TABLE ( CO_PRECIO int, PRECIO decimal, FX_INICIO datetime, FX_FIN datetime, CO_PRODUCTO int ) INSERT INTO PRECIOS (PRECIO, FX_INICIO, FX_FIN, CO_PRODUCTO) OUTPUT INSERTED.* INTO @FILAS_INSERTADAS VALUES (10, getdate(),getdate()+30, 1) SELECT * FROM @FILAS_INSERTADAS

Actualizar datos en Transact SQL

Update

Para la actualización de datos Transact SQL dispone de la sentencia UPDATE. La sentencia UPDATE permite la actualización de uno o varios registros de una única tabla. La sintaxis de la sentencia UPDATE es la siguiente

UPDATE SET = {[, = ,..., = ]} [ WHERE ];

El siguiente ejemplo muestra el uso de UPDATE.

UPDATE CLIENTES SET NOMBRE = 'Devjoker', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez' WHERE CO_CLIENTE = 10

Un aspecto a tener en cuenta, sobre todo si has trabajado con ORACLE, es que SQL graba los cambios inmediatamente sin necesidad de hacer COMMIT. Por supuesto podemos gestionar nosostros las transacciones pero es algo que hay que hacer de forma explicita con la instruccion BEGIN TRAN y que se verá en capitulos posteriores de este tutorial.

Update INNER JOIN

En ocasiones queremos actaualizar los datos de una tabla con los datos de otra (muy común para desnormalizar un modelo de datos).

Habitualmente, usamos subconsultas para este proposito, pero Transact SQL permite la utilización de la sentencia UPDATE INNER JOIN.

UPDATE CLIENTES SET NOMBRE = FICHERO_CLIENTES.NOMBRE, APELLIDO1 = FICHERO_CLIENTES.APELLIDO1, APELLIDO2 = FICHERO_CLIENTES.APELLIDO2 FROM CLIENTES INNER JOIN FICHERO_CLIENTES ON FICHERO_CLIENTES.CO_CLIENTE = CLIENTES.CO_CLIENTE

Clausula OUTPUT

A partir de la version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

DECLARE @FILAS_ACTUALIZADAS TABLE ( CO_CLIENTE int , NOMBRE varchar(100), APELLIDO1 varchar(100), APELLIDO2 varchar(100) ) UPDATE CLIENTES SET NOMBRE = 'Devjoker', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez' OUTPUT DELETED.* INTO @FILAS_ACTUALIZADAS WHERE CO_CLIENTE IN (10, 11, 12) SELECT * FROM @FILAS_ACTUALIZADAS

Las columnas con prefijo INSERTED reflejan el valor después de que se complete la instrucción UPDATE o INSERT, pero antes de que se ejecuten los desencadenadores. Es decir, son una copia de los datos "despues" del cambio.

INSERTED no se puede utilizar con la cláusula OUTPUT en la instrucción DELETE.

DECLARE @FILAS_ACTUALIZADAS TABLE ( CO_CLIENTE int , NOMBRE varchar(100), APELLIDO1 varchar(100), APELLIDO2 varchar(100) ) UPDATE CLIENTES SET NOMBRE = 'Devjoker', APELLIDO1 = 'Herrarte', APELLIDO2 = 'Sánchez' OUTPUT INSERTED.* INTO @FILAS_ACTUALIZADAS WHERE CO_CLIENTE IN (10, 11, 12) SELECT * FROM @FILAS_ACTUALIZADAS

Borrar datos en Transact SQL

Delete

Para borrar datos de una tabla debemos utilizar la sentencia DELETE.

Para ejecutar los ejemplos de este capitulo debemos ejecutar el siguiente script, que crea la tabla "DATOS" y carga registros en ella.

CREATE TABLE DATOS ( Id int identity not null, dato varchar(100), fx_alta datetime, constraint PK_DATOS PRIMARY KEY (Id) ) GO DECLARE @i int, @dato varchar(100) set @i = 0 WHILE (@i <100) BEGIN SET @i = @i +1 set @dato = 'Dato:' + cast(@i as varchar) INSERT INTO DATOS (dato, fx_alta) VALUES (@dato, getdate()) END GO SELECT * from DATOS

Para borrar los registros de la tabla "DATOS" ejecutaremos la siguiente instrucción. Notese que no se especifica ninguna condición WHERE por lo que se borran todos los datos de la tabla.

DELETE FROM DATOS

Lógicamente podemos especicar que registros queremos borrar a través de la clausula WHERE.

DELETE FROM DATOS WHERE Id=12

Cuando borramos datos de una tabla, podemos obtener el número de filas que han sido afectadas por la instrucción a través de la variable @@RowCount.

El siguiente ejemplo ilustra el uso de @@RowCount.

DELETE FROM DATOS WHERE Id=17 SELECT @@ROWCOUNT

Clausula OUTPUT

A partir de la version de SQL Server 2005 disponemos de la clausula OUTPUT para recuperar los valores que hemos insertado. Al igual que en un trigger disponemos de las tablas lógicas INSERTED y DELETED.

Las columnas con prefijo DELETED reflejan el valor antes de que se complete la instrucción UPDATE o DELETE. Es decir, son una copia de los datos "antes" del cambio.

DELETED no se puede utilizar con la cláusula OUTPUT en la instrucción INSERT.

DECLARE @FILAS_BORRADAS TABLE ( Id int, dato varchar(100), fx_alta datetime ) DELETE FROM DATOS OUTPUT DELETED.* INTO @FILAS_BORRADAS WHERE Id=17 SELECT * from @FILAS_BORRADAS

Truncate Table

Para borrar datos de forma masiva disponemos de la instrucción TRUNCATE TABLE, que borra todos los datos de una tabla.

TRUNCATE TABLE DATOS

Cuando trabajamos con TRUNCATE TABLE debemos tener en cuenta las siguientes consideraciones:

• TRUNCATE TABLE no admite la clausula WHERE.

No podemos ejecutar TRUNCATE TABLE sobre tablas que sean "padres" en foreign keys

Select FOR XML

Clausula FOR XML.

A partir de la version 2000 SQL Server incluye la clausula FOR XML para la consultas. Sin embargo, es a partir de la versión 2005 cuando se integra XML como tipo de dato nativo.

Cuando especificamos la clausula FOR XML el resultado de la consulta es devuelto en formato XML.

La clausula FOR XML admite los siguientes modos que representan el formato en el que el XML es devuelto:

• XML AUTO, el modo AUTO emplea los campos en la declaración SELECT para formar una jerarquía simple XML.
• XML RAW, el modo RAW genera elementos únicos, los cuales se denominan row, por cada fila retornada.
• EXPLICIT, el modo EXPLICIT requiere un formato específico que puede ser mapeado en casi cualquier forma XML, y al mismo tiempo ser formulado por una sola consulta SQL.

Adicionalmente, disponemos de dos opciones más TYPE y ELEMENTS que determinan el formato del XML resultante. Los vemos con ejemplos.

Un ejemplo de XML AUTO.

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA FOR XML AUTO, TYPE

Obtendremos el siguiente resultado:

Podemos obtener el resultado como elementos de la siguiente forma:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS FOR XML AUTO, ELEMENTS

Obtendremos el siguiente resultado:

1 FAMILIA 1 2 FAMILIA 2 3 FAMILIA 3 4 FAMILIA 4

Ahora un ejemplo de XML RAW:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA FOR XML RAW , TYPE

Obtenemos el siguiente resultado:

Podemos obtener el resultado como elementos de la siguiente forma:

1 FAMILIA 1 2 FAMILIA 2 3 FAMILIA 3 4 FAMILIA 4

También es posible especificar el nodo que queremos que muestre:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA FOR XML RAW ('FamiliasDeProductos') , TYPE

Devuelve el siguiente resultado:

Del mismo modo con la opción ELEMENTS:

SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS ORDER BY FAMILIA FOR XML RAW ('FamiliasDeProductos') , ELEMENTS

Obtendremos el siguiente resultado:

1 FAMILIA 1 2 FAMILIA 2 3 FAMILIA 3 4 FAMILIA 4

Ahora un ejemplo con el formato XML EXPLICIT.

SELECT 1 AS TAG, -- La primera columna debe tener el alias TAG NULL AS PARENT, -- La segunda columna debe tener el alias PARENT -- El resto de columnas deben tener el alias en el formato: -- !! CO_FAMILIA as "FamiliaDeProductos!1!CODIGO_FAMILIA", FAMILIA as "FamiliaDeProductos!1!DESCRIPCION" FROM FAMILIAS ORDER BY FAMILIA FOR XML EXPLICIT

Obtenemos el siguiente resultado:

Campos y variables XML.

Dado que XML es un tipo nativo de XML podemos definir tablas con campos de tipo XML, variables ...

El siguiente ejemplo muestra como trabajar con campos y variables XML.

-- Primero creamos una tabla con un campo XML CREATE TABLE tablaXML ( ID int not null identity, DOC xml null, constraint PK_tablaXML PRIMARY KEY (ID) ) GO DECLARE @xml xml -- Variable de tipo XML -- Leemos los datos de la tabla FAMILIAS SET @xml = (SELECT CO_FAMILIA, FAMILIA FROM FAMILIAS FOR XML AUTO) -- y los guardamos en nuestra tabla INSERT INTO tablaXML (DOC) VALUES (@xml) -- Hacemos lo mismo con los productos SET @xml = (SELECT * FROM PRODUCTOS FOR XML AUTO) INSERT INTO tablaXML (DOC) VALUES (@xml) -- Consultamos la tabla y vemos el resultado SELECT * FROM tablaXML

Cuando consultemos la tabla tendremos la siguiente información (en mi caso claro!):