Tuesday, June 08, 2010

COMBINAR DATOS AISLADOS EN CONSULTAS CON LA SENTENCIA UNION EN SQL SERVER 2000 - SQL SERVER 2005

La sentencia UNION sirve para entrelazar datos aislados independientemente del servidor donde se encuentre almacenadas las base de datos.

Creo que no hay mejor manera de explicar esta sentencia que con un ejemplo que se me viene a la mente ahora que me encuentro participando en un proyecto de software de predios para la Municipalidad Distrital De Agallpampa.

Suponiendo que un día se decide analizar los datos del sistema de predios de una municipalidad X con los de otra municipalidad Y con sistemas independientes, luego de migrar las tablas o información que se necesite cotejar (quizás con DTS) vamos a cruzar información:

Primero creamos las siguientes tablas:

CREATE TABLE t_distritos_trujillo (idd int, nombre nvarchar(20))
INSERT INTO t_distritos_trujillo VALUES (1, 'EL PORVENIR')
INSERT INTO t_distritos_trujillo VALUES (2, 'LA ESPERANZA')
INSERT INTO t_distritos_trujillo VALUES (3, 'LAREDO')

CREATE TABLE t_distritos_otuzco (codPredio bigint,distrito nvarchar(20), poblacion float)
INSERT INTO t_distritos_otuzco VALUES(1,'USQUIL', 12.000)
INSERT INTO t_distritos_otuzco VALUES(2,'AGALLPAMPA', 3.000)


Si deseo cruzar información utilizando la sentencia UNION aplico lo siguiente:

SELECT nombre as 'DISTRITOS DE LA LIBERTAD' FROM t_distritos_trujillo
UNION
SELECT distrito FROM t_distritos_otuzco










... Hasta mas leernos...
INSERTAR, MODIFICAR O ELIMINAR REGISTROS DESDE LAS VISTAS SQL SERVER 2000 - 2005

Este ejemplo funciona tanto para sql 2000 como 2005 gracias al desencadenador INSTEAD OF presente en ambas versiones.

Para empezar debemos tener claro que no es posible insertar a las tablas directamente desde las vistas, es por eso que debemos usar desencadenadores donde programamos la insersion, actualizacion o eliminación del registro. Una de las razones por la que tenemos que usar desencadenadores es para no crear inconsistencia de datos, es decir no duplicar información ya que las vistas son subyacentes a muchas tablas, es aqui donde entran a tallar los desencadenadores validando y verificando la información antes de ser manipulada.

El ejemplo que mostraré a continuación pertenece a los libros virtuales de microsoft.

Creamos el siguiente diagrama:

CREATE TABLE Person
(
SSN char(11) PRIMARY KEY,
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime
)

CREATE TABLE EmployeeTable
(
EmployeeID int PRIMARY KEY,
SSN char(11) UNIQUE,
Department nvarchar(10),
Salary money,
CONSTRAINT FKEmpPer FOREIGN KEY (SSN)
REFERENCES Person (SSN)
)


Y la siguiente vista:

CREATE VIEW Employee AS
SELECT P.SSN as SSN, Name, Address,
Birthdate, EmployeeID, Department, Salary
FROM Person P, EmployeeTable E
WHERE P.SSN = E.SSN


La siguiente tabla servirá para insertar los datos duplicados que se puedan producir desde la vista.

CREATE TABLE PersonDuplicates
(
SSN char(11),
Name nvarchar(100),
Address nvarchar(100),
Birthdate datetime,
InsertSNAME nchar(100),
WhenInserted datetime
)


Finalmente crearemos un desencadenador a la vista Employee para hacer inserciones de las tablas involucradas en la vista:

CREATE TRIGGER IO_Trig_INS_Employee ON Employee
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
-- Check for duplicate Person. If there is no duplicate, do an insert.
IF (NOT EXISTS (SELECT P.SSN
FROM Person P, inserted I
WHERE P.SSN = I.SSN))
INSERT INTO Person
SELECT SSN,Name,Address,Birthdate
FROM inserted
ELSE
-- Log an attempt to insert duplicate Person row in PersonDuplicates table.
INSERT INTO PersonDuplicates
SELECT SSN,Name,Address,Birthdate,SUSER_SNAME(),GETDATE()
FROM inserted
-- Check for duplicate Employee. If no there is duplicate, do an INSERT.
IF (NOT EXISTS (SELECT E.SSN
FROM EmployeeTable E, inserted
WHERE E.SSN = inserted.SSN))
INSERT INTO EmployeeTable
SELECT EmployeeID,SSN, Department, Salary
FROM inserted
ELSE
--If there is a duplicate, change to UPDATE so that there will not
--be a duplicate key violation error.
UPDATE EmployeeTable
SET EmployeeID = I.EmployeeID,
Department = I.Department,
Salary = I.Salary
FROM EmployeeTable E, inserted I
WHERE E.SSN = I.SSN
END

Para aclarar el ejemplo:

inserted viene a ser la tabla temporal de insercion, para los que conocen ya un poco de desencadenadores saben que en inserted están los datos que estan solicitando insertarse en nuestra base dedatos.

Como se ve en el desencadenador este comprueba que no exista el dato en ninguna de las dos tablas utilizando la NOT EXISTS.

Concluisiones personales.- Muchos pensarán que usar trigguers en vistas para poder insertar es un trabajo engorroso, mucho mas aín cuando necesitamos usar las funciones como NO EXISTS, pero son de gran utilidad y necesarias cuando tratamos de buscar compatibilidad con versiones anterioes de DBMS (gestores de base de datos), en donde quizas podemos encontrar tablas sin normalizar.

---
...Hasta mas leernos...