Por: Daniel Calbonte | Actualizado: 2019-05-22 | Comentarios (24) | Relacionados: 1 | 2 | 3 | 4 | Más > Comparación de datos y objetos
Problema
A veces necesitamos comparar tablas y/o datos de SQL Server para saber qué ha cambiado. Este consejo le muestra diferentes formas de comparar datos, tipos de datos y tablas usando SQLServer.
Solución
Le mostraré diferentes métodos para identificar los cambios. Digamos que tenemos dos tablas similares en diferentes bases de datos y queremos saber qué es diferente.
Aquí hay un script que crea bases de datos, tablas y datos de ejemplo.
CREATE DATABASE dbtest01GOUSE dbtest01GOCREATE TABLE . ( (10) NOT NULL, (10) NULL, (10) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GOINSERT INTO . VALUES ('001', '1', '40'), ('002', '2', '80'), ('003', '3', '120')GOCREATE DATABASE dbtest02GOUSE dbtest02GOCREATE TABLE . ( (10) NOT NULL, (10) NULL, (10) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GOINSERT INTO . VALUES ('001', '1', '40'), ('002', '2', '80'), ('003', '3', '120'), ('004', '4', '160')GO
El código T-SQL genera 2 tablas en diferentes bases de datos. Los nombres de las tablas son los mismos, pero la tabla de la base de datos dbtest02 contiene una fila extra como se muestra a continuación:
Veamos cómo podemos comparar estas tablas utilizando diferentes métodos.
Comparar datos de SQL Server en tablas utilizando un LEFT JOIN
Con el LEFT JOIN podemos comparar valores de columnas específicas que no son comunesentre dos tablas.
Por ejemplo:
SELECT *FROM dbtest02.dbo.article d2LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id
El LEFT JOIN muestra todas las filas de la tabla izquierda «dbtest02.dbo.article», aunque no haya coincidencias en la tabla «dbtest01.dbo.article»:
En este ejemplo, estamos comparando 2 tablas y se muestran los valores de NULLsi no hay filas coincidentes. Este método funciona para verificar nuevas filas, pero si actualizamos otras columnas, el LEFT JOIN no ayuda.
Esto se puede hacer en ambos sentidos para ver si hay diferencias en el otro sentido. Esta consulta sólo devolverá las 3 filas coincidentes.
SELECT *FROM dbtest01.dbo.article d1LEFT JOIN dbtest02.dbo.article d2 ON d1.id = d2.id
Comparar datos de SQL Server en tablas usando la cláusula EXCEPT
Except muestra la diferencia entre dos tablas (los chicos de Oracle usaneminus en lugar de except y la sintaxis y uso es el mismo). Se utiliza para comparar las diferencias entre dos tablas. Por ejemplo, veamos las diferencias entre las dos tablas:
Ahora vamos a ejecutar una consulta usando except:
SELECT * FROM dbtest02.dbo.articleEXCEPTSELECT * FROM dbtest01.dbo.article
El except devuelve la diferencia entre las tablas de dbtest02 y dbtest01:
Si volteamos las tablas en la consulta no veremos ningún registro, porque la tabla de la base de datos dbtest02 tiene todos los registros más uno extra.
SELECT * FROM dbtest01.dbo.articleEXCEPTSELECT * FROM dbtest02.dbo.article
Este método es mejor que el primero, porque si cambiamos los valores de otras columnas como el tipo y el coste, EXCEPT notará la diferencia.
Este es un ejemplo si actualizamos el id «001» en la base de datos dbtest01 y cambiamos el coste de «40» a «1». Si actualizamos los registros y luego ejecutamos la consulta de nuevo veremos estas diferencias ahora:
Desgraciadamente no crea un script para sincronizar las tablas.
Comparar datos de SQL Server en tablas usando la herramienta Tablediff
Hay una buena herramienta de línea de comandos que se usa para comparar tablas. Se puede encontrar en la carpeta «C:\NArchivos de programa\Nde Microsoft SQL Server\110\COM». Esta herramienta de línea de comandos se utiliza para comparar las tablas. También genera un script con las sentencias INSERT, UPDATE y DELETE para sincronizar las tablas. Para obtener más detalles, consulte este artículo.
Comparar los datos de SQL Server en las tablas mediante la captura de datos de cambios (CDC)
Esta función está disponible en SQL Server 2008 y posteriores. Necesita habilitar esta característica y también necesita tener el Agente de SQL Server en ejecución. Básicamente, crea tablas del sistema que rastrean los cambios en las tablas que desea supervisar. No compara las tablas, pero rastrea los cambios en las tablas.
Para obtener más información, consulte estos consejos de Captura de datos de cambios (CDC).
Comparar tipos de datos de SQL Server entre dos tablas
¿Qué sucede si queremos comparar los tipos de datos? ¿Existe una forma de comparar los tipos de datos?
Sí, podemos utilizar las vistas del sistema . para verificar ycomparar la información. Vamos a crear una nueva tabla llamada dbo.article2 conuna columna con diferente tipo de datos que la tabla dbo.article:
USE dbtest01GOCREATE TABLE .( NOT NULL, nchar(10) NULL, nchar(10) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GO
La diferencia es que el id es ahora un int en lugar denchar(10) como las otras tablas.
La consulta para comparar los tipos de datos de las tablas article y article1 sería:
USE dbtest01GOSELECT c1.table_name, c1.COLUMN_NAME, c1.DATA_TYPE, c2.table_name, c2.DATA_TYPE, c2.COLUMN_NAMEFROM . c1LEFT JOIN . c2 ON c1.COLUMN_NAME = c2.COLUMN_NAMEWHERE c1.TABLE_NAME = 'article' AND c2.TABLE_NAME = 'article2' AND c1.data_type <> c2.DATA_TYPE
Los resultados son los siguientes:
La consulta compara los tipos de datos de estas dos tablas. Estamos comparando la tabla «article» con la tabla «article2» y mostrando si alguno de los tipos de datos es diferente.
Comparar si hay columnas extra entre las tablas de SQL Server
A veces necesitamos asegurarnos de que dos tablas contienen el mismo número de columnas.Para ilustrar esto vamos a crear una tabla llamada «artículo3» con 2 extracolumnas llamadas extra1 y extra2:
USE dbtest01GOCREATE TABLE .( NOT NULL, nchar(10) NULL, nchar(10) NULL, extra1 int, extra2 int)
Para comparar las columnas voy a utilizar esta consulta:
USE dbtest01GOSELECT c2.table_name, c2.COLUMN_NAMEFROM . c2WHERE table_name = 'article3' AND c2.COLUMN_NAME NOT IN ( SELECT column_name FROM . WHERE table_name = 'article' )
La consulta compara las diferentes columnas entre la tabla «article» y «article3».Las diferentes columnas son extra1 y extra2. Este es el resultado de la consulta:
Comparar tablas de SQL Server en diferentes bases de datos
Ahora vamos a comparar las tablas de la base de datos dbtest01 y dbtest02. La siguiente consulta mostrará las diferentes tablas de dbtest01 comparadas con las de dbtest02:
SELECT 'dbtest01' AS dbname, t1.table_nameFROM dbtest01.. t1WHERE table_name NOT IN ( SELECT t2.table_name FROM dbtest02.. t2 )UNIONSELECT 'dbtest02' AS dbname, t1.table_nameFROM dbtest02.. t1WHERE table_name NOT IN ( SELECT t2.table_name FROM dbtest01.. t2 )
Herramientas de terceros
Existen grandes herramientas para comparar tablas incluyendo datos y esquemas. Puede utilizarVisualStudio o utilizar otras herramientas de comparación de servidoresSQL.
Siguientes pasos
- Hay múltiples herramientas y formas de comparar datos y esquemas. Seleccione el método que mejor se adapte a sus necesidades.
- Para más información consulte estos enlaces:
- SQL Server Except
- SQL Server Left Join
- SQL Server Change Data Capture
- SQL Server Tablediff Utility
Last Updated: 2019-05-22
Acerca del autor
Daniel Calbimonte es un MVP de Microsoft SQL Server, formador certificado por Microsoft y profesional de TI certificado por Microsoft.Ver todos mis consejos