Da: Daniel Calbimonte | Aggiornato: 2019-05-22 | Commenti (24) | Correlati: 1 | 2 | 3 | 4 | Altro > Confronto dati e oggetti
Problema
A volte abbiamo bisogno di confrontare tabelle e/o dati SQL Server per sapere cosa è stato cambiato. Questo suggerimento mostra diversi modi per confrontare dati, tipi di dati e tabelle usando SQLServer.
Soluzione
Vi mostrerò diversi metodi per identificare i cambiamenti. Diciamo che abbiamo due tabelle simili in diversi database e vogliamo sapere cosa è diverso.
Qui c’è uno script che crea database, tabelle e dati di esempio.
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
Il codice T-SQL genera 2 tabelle in diversi database. I nomi delle tabelle sono gli stessi, ma la tabella nel database dbtest02 contiene una riga in più come mostrato qui sotto:
Vediamo come possiamo confrontare queste tabelle usando diversi metodi.
Confrontare i dati di SQL Server nelle tabelle usando una LEFT JOIN
Con la LEFT JOIN possiamo confrontare i valori di colonne specifiche che non sono comuni tra due tabelle.
Per esempio:
SELECT *FROM dbtest02.dbo.article d2LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id
La left join mostra tutte le righe della tabella sinistra “dbtest02.dbo.article”, anche se non ci sono corrispondenze nella tabella “dbtest01.dbo.article”:
In questo esempio, stiamo confrontando 2 tabelle e i valori di NULL sono visualizzati se non ci sono righe corrispondenti. Questo metodo funziona per verificare le nuove righe, ma se aggiorniamo altre colonne, la LEFT JOIN non aiuta.
Questo può essere fatto in entrambi i modi per vedere se ci sono differenze nell’altro modo. Questa query restituirà solo le 3 righe corrispondenti.
SELECT *FROM dbtest01.dbo.article d1LEFT JOIN dbtest02.dbo.article d2 ON d1.id = d2.id
Confrontare i dati di SQL Server nelle tabelle usando la clausola EXCEPT
Except mostra la differenza tra due tabelle (quelli di Oracle usano il termine except al posto di except e la sintassi e l’uso sono gli stessi). Si usa per confrontare le differenze tra due tabelle. Per esempio, vediamo le differenze tra le due tabelle:
Ora eseguiamo una query usando except:
SELECT * FROM dbtest02.dbo.articleEXCEPTSELECT * FROM dbtest01.dbo.article
L’except restituisce la differenza tra le tabelle di dbtest02 e dbtest01:
Se giriamo le tabelle nella query non vedremo nessun record, perché la tabella del database dbtest02 ha tutti i record più uno in più.
SELECT * FROM dbtest01.dbo.articleEXCEPTSELECT * FROM dbtest02.dbo.article
Questo metodo è migliore del primo, perché se cambiamo i valori di altre colonne come il tipo e il costo, EXCEPT noterà la differenza.
Ecco un esempio se aggiorniamo l’id “001” nel database dbtest01 e cambiamo il costo da “40” a “1”. Se aggiorniamo i record e poi eseguiamo di nuovo la query vedremo queste differenze ora:
Purtroppo non crea uno script per sincronizzare le tabelle.
Compara i dati di SQL Server nelle tabelle usando lo strumento Tablediff
C’è un bello strumento a riga di comando usato per confrontare le tabelle. Questo può essere trovato nella cartella “C:\Program Files\Microsoft SQL Server\110\COM\”. Questo strumento a riga di comando è usato per confrontare le tabelle. Genera anche uno script con le istruzioni INSERT, UPDATE e DELETE per sincronizzare le tabelle. Per maggiori dettagli, fate riferimento a questo articolo.
Compara i dati di SQL Server nelle tabelle usando Change Data Capture (CDC)
Questa funzione è disponibile in SQL Server 2008 e successivi. Devi abilitare questa funzione e devi anche avere SQL Server Agent in esecuzione. Fondamentalmente crea tabelle di sistema che tengono traccia dei cambiamenti nelle vostre tabelle che volete monitorare. Non confronta le tabelle, ma tiene traccia dei cambiamenti nelle tabelle.
Per maggiori informazioni, fate riferimento a questi consigli Change Data Capture (CDC).
Confronta i tipi di dati di SQL Server tra due tabelle
Cosa succede se vogliamo confrontare i tipi di dati? C’è un modo per confrontare i tipi di dati?
Sì, possiamo usare le viste di sistema per verificare e confrontare le informazioni. Creeremo una nuova tabella chiamata dbo.article2 con una colonna con un tipo di dati diverso dalla tabella 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 differenza è che l’id è ora un int invece dinchar(10) come le altre tabelle.
La query per confrontare i tipi di dati delle tabelle article e article1 sarebbe:
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
I risultati sono i seguenti:
La query confronta i tipi di dati di queste due tabelle. Stiamo confrontando la tabella “article” con la tabella “article2” e mostrando se qualche tipo di dato è diverso.
Confronta se ci sono colonne extra tra le tabelle di SQL Server
A volte dobbiamo assicurarci che due tabelle contengano lo stesso numero di colonne.Per illustrare questo creeremo una tabella chiamata “article3” con 2 extracolonne chiamate extra1 e extra2:
USE dbtest01GOCREATE TABLE .( NOT NULL, nchar(10) NULL, nchar(10) NULL, extra1 int, extra2 int)
Per confrontare le colonne userò questa query:
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 query confronta le diverse colonne tra la tabella “articolo” e “articolo3”.Le diverse colonne sono extra1 e extra2. Questo è il risultato della query:
Confronta le tabelle di SQL Server in diversi database
Ora confrontiamo le tabelle nei database dbtest01 e dbtest02. La seguente query mostrerà le diverse tabelle in dbtest01 confrontate con 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 )
Strumenti di terze parti
Ci sono ottimi strumenti per confrontare tabelle, inclusi dati e schemi. Puoi usare VisualStudio o altri strumenti di confronto per SQL Server.
Passi successivi
- Ci sono più strumenti e modi per confrontare dati e schemi. Scegliete il metodo che funziona meglio per le vostre esigenze.
- Per maggiori informazioni fare riferimento a questi link:
- SQL Server Except
- SQL Server Left Join
- SQL Server Change Data Capture
- SQL Server Tablediff Utility
L’ultimo aggiornamento: 2019-05-22
Informazioni sull’autore
Daniel Calbimonte è un Microsoft SQL Server MVP, Microsoft Certified Trainer e Microsoft Certified IT Professional.Vedi tutti i miei consigli