Door: Daniel Calbimonte | Bijgewerkt: 2019-05-22 | Reacties (24) | Gerelateerd: 1 | 2 | 3 | 4 | Meer > Vergelijking Gegevens en Objecten
Probleem
Soms moeten we SQL Server tabellen en/of gegevens vergelijken om te weten wat er is gewijzigd. Deze tip laat verschillende manieren zien om data, datatypes en tabellen te vergelijken met behulp van SQLServer.
Oplossing
Ik zal u verschillende methoden laten zien om wijzigingen te identificeren. Stel dat we twee vergelijkbare tabellen in verschillende databases hebben en we willen weten wat er verschilt.
Hier is een script dat voorbeelddatabases, tabellen en gegevens aanmaakt.
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
De T-SQL code genereert 2 tabellen in verschillende databases. De tabelnamen zijn hetzelfde, maar de tabel in database dbtest02 bevat een extra rij zoals hieronder te zien is:
Laten we eens kijken hoe we deze tabellen kunnen vergelijken met behulp van verschillende methoden.
Vergelijk SQL Server Data in Tabellen met behulp van een LEFT JOIN
Met de LEFT JOIN kunnen we waarden vergelijken van specifieke kolommen die niet gemeenschappelijk zijn tussen twee tabellen.
Voorbeeld:
SELECT *FROM dbtest02.dbo.article d2LEFT JOIN dbtest01.dbo.article d1 ON d2.id = d1.id
De linkse join toont alle rijen uit de linkse tabel “dbtest02.dbo.article”, zelfs als er geen overeenkomsten zijn in tabel “dbtest01.dbo.article”:
In dit voorbeeld vergelijken we 2 tabellen en worden de waarden van NULL weergegeven als er geen overeenkomende rijen zijn. Deze methode werkt om nieuwe rijen te controleren, maar als we andere kolommen bijwerken, helpt de LEFT JOIN niet.
Dit kan op beide manieren worden gedaan om te zien of er omgekeerd ook verschillen zijn. Deze query retourneert alleen de 3 overeenkomende rijen.
SELECT *FROM dbtest01.dbo.article d1LEFT JOIN dbtest02.dbo.article d2 ON d1.id = d2.id
Vergelijk SQL Server-gegevens in tabellen met behulp van de EXCEPT-clausule
Except toont het verschil tussen twee tabellen (de Oracle-jongens gebruiken eminus in plaats van except en de syntaxis en het gebruik zijn hetzelfde). Het wordt gebruikt om de verschillen tussen twee tabellen te vergelijken. Laten we bijvoorbeeld de verschillen tussen de twee tabellen bekijken:
Nu voeren we een query uit met except:
SELECT * FROM dbtest02.dbo.articleEXCEPTSELECT * FROM dbtest01.dbo.article
De except geeft het verschil tussen de tabellen van dbtest02 en dbtest01:
Als we de tabellen in de query omdraaien, krijgen we geen records te zien, omdat de tabel in database dbtest02 alle records plus één extra bevat.
SELECT * FROM dbtest01.dbo.articleEXCEPTSELECT * FROM dbtest02.dbo.article
Deze methode is beter dan de eerste, want als we de waarden voor andere kolommen, zoals het type en de kosten, wijzigen, zal EXCEPT het verschil opmerken.
Hier is een voorbeeld als we id “001” in database dbtest01 bijwerken en de kosten veranderen van “40 “in “1”. Als we de records bijwerken en dan de query opnieuw uitvoeren, zien we nu deze verschillen:
Het maakt helaas geen script om de tabellen te synchroniseren.
Vergelijk SQL Server-gegevens in tabellen met behulp van de Tablediff Tool
Er is een aardige opdrachtregel-tool waarmee tabellen kunnen worden vergeleken. Deze kan worden gevonden in de map “C:Program Files Microsoft SQL Server”. Deze opdrachtregel-tool wordt gebruikt om tabellen te vergelijken. Het genereert ook een script met de INSERT, UPDATE enDELETE statements om de tabellen te synchroniseren. Voor meer details, zie ditablediffartikel.
Vergelijk SQL Server-gegevens in tabellen met behulp van Change Data Capture (CDC)
Deze functie is beschikbaar in SQL Server 2008 en later. U moet deze functie inschakelen en u moet ook SQL Server Agent hebben draaien. In principe worden systeemtabellen aangemaakt die de wijzigingen in de tabellen bijhouden die u wilt controleren. Het vergelijkt geen tabellen, maar volgt de wijzigingen in tabellen.
Voor meer informatie, raadpleeg dezeChange Data Capture (CDC) tips.
Vergelijk SQL Server Data Types Tussen Twee Tabellen
Wat gebeurt er als we de data types willen vergelijken? Is er een manier om de datatypes te vergelijken?
Ja, we kunnen de . systeemweergaven gebruiken om de informatie te controleren en te vergelijken. We gaan een nieuwe tabel maken met de naam dbo.article2 met een kolom van een ander gegevenstype dan de dbo.article-tabel:
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
Het verschil is dat het id nu een int is in plaats vannchar(10) zoals in de andere tabellen.
De query om de gegevenstypen van de tabellen article en article1 te vergelijken zou zijn:
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
De resultaten zijn als volgt:
De query vergelijkt de gegevenstypen van deze twee tabellen. We vergelijken de tabel “article” met tabel “article2” en laten zien of de datatypes verschillen.
Vergelijken of er extra kolommen zijn tussen SQL Server Tabellen
Soms moeten we er zeker van zijn dat twee tabellen hetzelfde aantal kolommen bevatten.Om dit te illustreren gaan we een tabel maken met de naam “article3” met 2 extra kolommen met de namen extra1 en extra2:
USE dbtest01GOCREATE TABLE .( NOT NULL, nchar(10) NULL, nchar(10) NULL, extra1 int, extra2 int)
Om de kolommen te vergelijken zal ik deze query gebruiken:
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' )
De query vergelijkt de verschillende kolommen tussen tabel “article” en “article3”.De verschillende kolommen zijn extra1 en extra2. Dit is het resultaat van de query:
Vergelijk SQL Server-tabellen in verschillende databases
Nu gaan we de tabellen in database dbtest01 en dbtest02 vergelijken. De volgende query toont de verschillende tabellen in dbtest01 vergeleken met 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 )
Tools van derden
Er zijn geweldige tools om tabellen te vergelijken, inclusief gegevens en schema’s. U kunt VisualStudio gebruiken of andereSQL Server-vergelijkingstools.
Volgende stappen
- Er zijn meerdere tools en manieren om gegevens en schema’s te vergelijken. Kies de methode die het beste werkt voor uw behoeften.
- Voor meer informatie raadpleegt u deze links:
- SQL Server Except
- SQL Server Left Join
- SQL Server Change Data Capture
- SQL Server Tablediff Utility
Laatst bijgewerkt: 2019-05-22
Over de auteur
Daniel Calbimonte is een Microsoft SQL Server MVP, Microsoft Certified Trainer en Microsoft Certified IT Professional.Bekijk al mijn tips