DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to compare two tables for differences?

Execute the following Microsoft SQL Server 2008 T-SQL scripts in Query Editor to demonstrate the comparison of two tables for differences in rows and/or columns (cells).

------------

-- SQL SERVER COMPARE 2 TABLES FOR ROW & COLUMN DIFFERENCES

------------

-- TEMPLATE - SQL Server T-SQL compare two tables
SELECT Label='Found IN Table1, NOT IN Table2',* FROM
(SELECT * FROM Table1
 EXCEPT
 SELECT  * FROM Table2) x
UNION ALL
SELECT Label='Found IN Table2, NOT IN Table1',* FROM
(SELECT  * FROM Table2
 EXCEPT
 SELECT * FROM Table1) y

GO

------------

-- SQL Server T-SQL compare Product tables for 2005 & 2008
SELECT Label='Found IN AW8PP, NOT IN AWPP',* FROM
(SELECT * FROM AdventureWorks2008.Production.Product
 EXCEPT
 SELECT  * FROM AdventureWorks.Production.Product) x
UNION ALL
SELECT Label='Found IN AWPP, NOT IN AW8PP',* FROM
(SELECT  * FROM AdventureWorks.Production.Product
 EXCEPT
 SELECT * FROM AdventureWorks2008.Production.Product) y
GO

-- The two tables are identical
-- (0 row(s) affected
------------

-- SQL find rows present in both tables
SELECT * FROM AdventureWorks2008.Production.Product
INTERSECT
SELECT * FROM AdventureWorks.Production.Product
-- (504 row(s) affected)
------------

------------
-- ALL SQL Server T-SQL versions
------------
-- SQL finding rows missing in source table based on PRIMARY KEY JOIN to target table
USE tempdb;
SELECT * INTO Product1 FROM Northwind.dbo.Products
SELECT * INTO Product2 FROM Northwind.dbo.Products
DELETE Product2 WHERE UnitPrice > 100.0
-- (2 row(s) affected)
 
SELECT * FROM Product1 p1
WHERE NOT EXISTS (SELECT * FROM Product2  p2
                  WHERE p2.ProductId = p1.ProductId)
GO
/* Partial results
ProductID   ProductName
29    Thüringer Rostbratwurst
38    Côte de Blaye
*/
 
-- Alternate  query - same results
SELECT * FROM Product1  p1
  LEFT OUTER JOIN Product2  p2
    ON p2.ProductId = p1.ProductId
WHERE p2.ProductId IS NULL
GO
-- (2 row(s) affected)
DROP TABLE tempdb.dbo.Product1
DROP TABLE tempdb.dbo.Product2

------------

Related articles:

The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !

SQL Server 2005: Using EXCEPT and INTERSECT to compare tables

Comparison Query to Compare Two SQL Server Tables

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE