How to compare two tables for differences in SQL Server?

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

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