verify_script.sql #blog

verify_script.sql

-- http://spaghettidba.com/2012/03/15/how-to-eat-a-sql-elephant/
-- =============================================
-- Author:      Gianluca Sartori - spaghettidba
-- Create date: 2012-03-14
-- Description: Runs two T-SQL statements and 
--              compares the results
-- =============================================

-- Drop temporary tables
IF OBJECT_ID('tempdb..#original') IS NOT NULL 
	DROP TABLE #original;

IF OBJECT_ID('tempdb..#rewritten') IS NOT NULL 
	DROP TABLE #rewritten;

-- Store the results of the original 
-- query into a temporary table
WITH original AS (
	<original, text, >
)
SELECT *
INTO #original
FROM original;

-- Add a sort column
ALTER TABLE #original ADD [______sortcolumn] int identity(1,1);



-- Store the results of the rewritten 
-- query into a temporary table
WITH rewritten AS (
	<rewritten, text, >
)
SELECT *
INTO #rewritten
FROM rewritten;

-- Add a sort column
ALTER TABLE #rewritten ADD [______sortcolumn] int identity(1,1);


-- Compare the results
SELECT 'original' AS source, *
FROM (
	SELECT * 
	FROM #original 
	
	EXCEPT 
	
	SELECT * 
	FROM #rewritten
) AS A

UNION ALL

SELECT 'rewritten' AS source, *
FROM (
	SELECT * 
	FROM #rewritten 
	
	EXCEPT 
	
	SELECT * 
	FROM #original
) AS B;