Tema 14: Modificar datos en una Base de Datos SQL Server

Un componente esencial de un sistema de bases de datos es la capacidad para modificar los datos que se encuentran almacenados dentro del sistema. SQL Server soporta métodos para agregar filas de datos a las tablas de una base de datos SQL Server, cambiar los datos en las filas existentes, y eliminar filas.

Insertar datos en un base de datos SQL Server

SQL Server varios métodos para insertar datos a una base de datos:

• El comando INSERT
• El comando SELECT...INTO
• El comando WRITETEXT y varias opciones en la API (Application Programming Interface) de la base de datos, que se pueden usar para agregar texto e imágenes a una fila.

NOTA

Un comando INSERT trabaja tanto sobre tablas como sobre vistas (con algunas restricciones).

Usar el comando INSERT para agregar datos

El comando INSERT agrega una o más nuevas filas a una tabla. En un tratamiento simplificado, el comando INSERT toma la siguiente forma:

INSERT [INTO] tabla_o_vista [(lista_de_columnas)] valores_de_datos

Este comando hace que los valores de los datos (valores_de_datos) sean insertados como una o mas filas en la tabla o vista. La lista de los nombres de columnas (lista_de_columnas), separadas por comas, se usan para especificar las columnas que recibirán los datos. Si no se indican columnas, todas las columnas de la tabla o vista recibirán datos. Si solo se indica una lista parcial de columnas, el resto de las columnas recibirán un valor nulo o el valor configurado por defecto para esa columna, en caso que lo tenga.

Además, no se deben asignar valores a los siguientes tipos de columnas, dado que SQL Server genera automáticamente este valor.

• Columnas con la propiedad IDENTITY
• Columnas con una definición DEFAULT que use la función NEWID()
• Columnas computadas

NOTA

La palabra clave INTO en un comando INSERT es opcional y solo se utiliza para clarificar el código.

Los valores ingresados deben coincidir con la lista de columnas. La cantidad de valores provistos debe ser igual a la cantidad de columnas indicadas en la lista de columnas, y el tipo de dato, precisión, y escala de cada valor debe coincidir con los de las columnas correspondientes.

Cuando se define un comando INSERT, se puede usar la cláusula VALUES para especificar los valores de los datos para una fila o usar una subconsulta SELECT para especificar los valores para una o más columnas.

Usar el comando INSERT...VALUES para agregar datos

Una cláusula VALUES permite especificar los valores para una fila de la tabla. Los valores son indicados a través de una lista de expresiones escalares separadas por comas. Estos valores deben ser implícitamente convertibles al tipo, precisión y escala de las columnas correspondientes. Si no se especifica la lista de columnas, los datos deben ser ingresados en el mismo orden que tiene en la definición de la tabla o vista.

Por ejemplo, suponga que se crea la siguiente tabla en la base de datos Pubs:

USE Pubs

CREATE TABLE NuevosLibros

(

LibroID INT IDENTITY(1,1) NOT NULL,

LibroTitulo VARCHAR(80) NOT NULL,

LibroTipo CHAR(12) NOT NULL

CONSTRAINT [tipolibro_df] DEFAULT ('no informado'),

CiudadEd VARCHAR(50) NULL

)

Una vez creada la tabla, se decide ingresar datos a una fila de la tabla. EL siguiente comando INSERT utiliza la cláusula VALUES para insertar una nueva fila en la tabla NuevosLibros:

USE Pubs

INSERT INTO NuevosLibros (LibroTitulo, CiudadEd)

VALUES ('Life Without Fear', 'Chicago')

En este comando, los valores han sido definidos para la columnas LibroTitulo y CiudadEd. Sin embargo, no es necesario incluir la columna LibroID en el comando INSERT, dado que la columna LibroID se define con la propiedad IDENTITY, porque los valores para esa columna se generan automáticamente. Además, al no ingresarse un valor para la columna TipoLibro, SQL Server automáticamente inserta el valor por defecto (no informado) en la columna al ejecutar el comando.

Usar una subconsulta SELECT para agregar datos

Se puede usar una subconsulta SELECT dentro de un comando INSERT para agregar datos a una tabla desde otra u otras tablas o vistas. Una subconsulta permite agregar más de una fila a la vez.

NOTA: Una subconsulta SELECT en un comando INSERT se utiliza para agregar subconjuntos de datos existentes a una tabla, mientras que la cláusula VALUES se usa para guardar datos nuevos en una tabla.

El siguiente comando INSERT usa una subconsulta SELECT para insertar filas en la tabla NuevosLibros:

USE Pubs

INSERT INTO NuevosLibros (LibroTitulo, LibroTipo)

SELECT Titulo, Tipo

FROM Titulos

WHERE Tipo = 'mod_cook'

Este comando INSERT usa la salida de una subconsulta SELECT para proveer los datos que se usarán para ser insertados en la tabla NuevosLibros.

Usar el comando SELECT...INTO para agregar datos

EL comando SELECT INTO permite crear una nueva tabla y llenarla con el resultado del comando SELECT. Ver Tema 2 de este módulo

Agregar datos ntext, text, o imagen a filas ya insertadas

SQL Server incluye varios métodos para agregar valores ntext, text, o imagen a un fila:

• Se pueden especificar relativamente pequeños montos de datos en un comando INSERT de la misma forma que se especifican datos char, nchar, o binary.
• Utilizar el comando WRITETEXT, que permite actualización interactiva, y no registrada, de una columna existente tipo text, ntext, o image. Este comanado sobrescribe completamente cualquier dato preexistente en la columna que afecta. Un comando WRITETEXT no puede usarse sobre columnas de una vista.
• Las aplicaciones ADO (Active Data Object) pueden usar el método AppendChunk para especificar grandes cantidades de datos tipo ntext, text, o image.
• Las aplicaciones OLE DB, por su parte, utilizan la interfase ISequentialStream.
• Las aplicaciones ODBC (Open Database Connectivity) usan el SQLPutData para ingresar nuevos valores del tipo ntext, text, o image.
• Por último, las aplicaciones DB-Library usan la función Dbwritetext.

Modificar datos en una base de datos SQL Server

Una vez que se crean las tablas y que se ingresan datos, cambiar y actualizar los datos se convierte en una tarea de mantenimiento diario SQL Server provee varios métodos para cambiar datos en una tabla existente:

• El comando UPDATE
• La API de la base de datos y los cursores
• El comando UPDATETEXT

Estos procedimientos se pueden tanto a tablas como a vistas (con algunas restricciones)

Usar el comando UPDATE para modificar datos

El comando UPDATE puede cambiar datos en una columna simple, en grupos de columnas, o en todas las columnas de una tabla o vista. Este comando se puede usar también para actualizar filas en un servidor remoto, ya sea usando el nombre de un servidor conectado o las funciones OPENROWSET, OPENDATASOURCE, y OPENQUERY (siempre y cuando el proveedor OLE DB usada para acceder al servidor remoto soporte actualizaciones). Un comando UPDATE que referencia a una tabla o una vista puede cambiar los datos en sólo un tabla base por vez.

NOTAUna actualización será exitosa sólo si el nuevo valor es compatible con el tipo de datos de la columna y cumple con todas las restricciones asociadas a esta.

Las cláusulas más importantes del comando UPDATE son:

• SET
• WHERE
• FROM

Usar la cláusula SET para modificar datos.

SET indica que columna será actualizada y los nuevos valores que se guardarán. Los valores en las columnas indicadas serán actualizados con los valores provistos en la cláusula SET en todas las filas que cumplan con la condición de búsqueda especificada en la cláusula WHERE. Si no se especifica la cláusula WHERE, todas las columnas serán actualizadas.

Por ejemplo, el siguiente comando UPDATE incluye la cláusula SET que incrementa el precio de los libros en la tabla NuevosLibros un 10 por ciento:

USE Pubs

UPDATE NuevosLibros

SET Precio = Precio * 1.1

En este comando, no se usa la cláusula WHERE, por lo que todas las filas serán modificadas (salvo aquellas que tengan un valor nulo para la columna Precio).

Usar la cláusula WHERE para modificar datos

La cláusula WHERE realiza dos funciones:

• Indica las filas a actualizar
• Indica las filas de la tabla fuente que califican para proveer valores de actualización cuando se especifica la cláusula FROM.

Si no se indica la cláusula WHERE, todas las filas serán actualizadas.

En el siguiente comando UPDATE, la cláusula WHERE se utiliza para limitar la actualización a sólo aquellas filas que cumplen con la condición definida en la cláusula:

USE Pubs

UPDATE NuevosLibros

SET LibroTipo = 'popular'

WHERE LibroTipo = 'popular_comp'

Este comando cambia el nombre del tipo de libro de aquellas filas con el valor popular_comp por el de popular. Si no se hubiese puesto la cláusula WHERE todos los valores de LibroTipo tomarían el valor popular.

Usar una cláusula FROM para modificar los datos

Se puede usar la cláusula FROM para traer datos desde una o más tablas o vistas a la tabal que se quiere actualizar. Por ejemplo, en el siguiente comando UPDATE, la cláusula FROM incluye un inner join que combina los títulos en las tablas NuevosLibros y Titulos:

USE Pubs

UPDATE NuevosLibros

SET Precio = Titulos.Precio

FROM NuevosLibros JOIN Titulos

ON NuevosLibros.LibroTitulo = Titulos.Titulo

En este comando, los valores de Precio en la tabla LibrosNuevos se actualizan al mismo valor que la columna Precio de la tabla Titulos.

Usar APIs y cursores para modificar datos

Las APIs ADO, OLE DB, y ODBC soportan actualizar los datos de la fila actual sobre la que la aplicación se encuentra posicionada en un conjunto de resultados. Además, cuando se usa un cursor del servidor Transact-SQL, se puede actualizar la fila actual utilizando el comando UPDATE con la cláusula WHERE CURRENT OF. Los cambios realizados con esta cláusula sólo afectarán a la fila donde se encuentre posicionado el cursor. Este punto se verá más en detalle en otro módulo del Kit.

Modificar datos ntext, text, o image

SQL Server provee varios métodos para modificar valores del tipo ntext, text, o image en una fila cuando se reemplazan los valores completos:

• Se pueden especificar relativamente pequeños montos de datos en un comando UPDATE de la misma forma que se modifican datos char, nchar, o binary.
• Utilizar los comandos WRITETEXT o UPDATETEXT, para modificar datos ntext, text, o image.
• Las aplicaciones ADO (Active Data Object) pueden usar el método AppendChunk para especificar grandes cantidades de datos tipo ntext, text, o image.
• Las aplicaciones OLE DB, por su parte, utilizan la interfase ISequentialStream.
• Las aplicaciones ODBC (Open Database Connectivity) usan el SQLPutData para ingresar nuevos valores del tipo ntext, text, o image.
• Por último, las aplicaciones DB-Library usan la función Dbwritetext.

SQL Server soporta, además, actualizaciones de sólo una porción de datos del tipo ntext, text, o image. En DB-Library, se puede hacer usando la función Dbupdatetext. Todas las otras aplicaciones y los scripts Transact-SQL, batches, procedimientos almacenados y disparadores pueden usar el comando UPDATETEXT para actualizar sólo una porción de columnas ntext, text, o image.

Eliminar datos de una base de datos SQL Server

SQL Server soporta varios métodos para eliminar datos de una tabla:

• El comando DELETE
• APIs y cursores
• El comando TRUNCATE TABLE

Usar el comando DELETE para eliminar datos

Un comando DELETE remueve una o más filas en una tabla o vista. La sintaxis siguiente es una forma simplificada del comando DELETE:

DELETE tabla_o_vista FROM tabla_fuente WHERE condicion_de_busqueda

En tabla_o_vista se colocan los nombre de la tabla o vista en que serán eliminadas las filas. Serán eliminadas todas las filas que cumplan con la condición de búsqueda especificada en la cláusula WHERE. Si esta no se indica, se eliminarán todas las filas de la tabla o vista. La cláusula FROM indica tablas, vistas o combinaciones adicionales que se pueden usar en el predicado de condición de la cláusula WHERE. No se borran las filas de las tablas indicadas en la cláusula FROM, sino sólo de la especificada en la cláusula DELETE.

Si a una tabla se le eliminan todas tablas las misma permanece en la base de datos de todos modos. Para eliminar la tabla de la base de datos se debe usar el comando DROP TABLE.

Considere el comando DELETE siguiente:

USE Pubs

DELETE NuevosLibros

FROM Titulos

WHERE NuevosLibros.LibroTitulo = Titulos.Titulo

AND Titulos.Royalty = 10

En este comando, se borrarán aquellas filas de la tabla NuevosLibros con un royalty del 10 por ciento. Este valor de royalty se toma de la columna royalty de la tabla Titulos.

Usar APIs y Cursores para eliminar datos

Las APIs ADO, OLE DB, y ODBC soportan eliminación de la fila actual sobre la cual se encuentra posicionada la aplicación en el conjunto de resultados. Además, cuando se usa un cursor del servidor Transact-SQL, se puede eliminar la fila actual utilizando el comando UPDATE con la cláusula WHERE CURRENT OF. Los cambios realizados con esta cláusula sólo afectarán a la fila donde se encuentre posicionado el cursor. Este punto se verá más en detalle en otro módulo del Kit.

Usar el comando TRUNCATE TABLE para eliminar datos

El comando TRUNCATE TABLE es una forma rápida y no registrada de eliminar todas las filas en una tabla. Este método es casi siempre más rápido que un comando DELETE sin condiciones, porque DELETE graba un registro de transacciones por cada fila eliminada, mientras que TRUNCATE TABLE solo graba registro de liberación de las páginas de datos completas. El comando TRUNCATE TABLE inmediatamente libera el espacio ocupado por los datos de la tabla e índices. También se liberan las páginas de distribución de todos los índices.

El comando TRUNCATE TABLE siguiente elimina todas las filas de la tabla NuevosLibros en la base de datos Pubs:

USE Pubs

TRUNCATE TABLE NuevosLibroswBooks

Como en el caso del comando DELETE, la definición de la tabla se mantiene en la base de datos después que se aplica un comando TRUNCATE TABLE (incluidos sus índices y objetos asociados). Para eliminar la definición de la tabla se debe usar el comando DROP TABLE.