Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

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

When you have two tables (or resultsets from SELECT statements) that you wish to compare, and you want to see any changes in ANY columns, as well as to see which rows exist in 1 table but not the other (in either direction) I have found that the UNION operator works quite well.

UNION allows you to compare all columns very quickly, and also handles comparing NULL values to other NULLs successfully, which a join clause or a WHERE condition doesn't normally do.  It also allows you to very quickly see which rows are missing in either table, which only a FULL OUTER JOIN will do, but of course we all know to avoid those at all costs (right?) -- a full outer join is about as “unrelational” as you can get.  (every column returned is potentially Null and must be wrapped in a COALESCE function).  Best of all, the UNION is quick and easy and short.

The basic idea is: if we GROUP the union of two tables on all columns, then if the two tables are identical all groups will result in a COUNT(*) of 2.  But for any rows that are not completely matched on any column in the GROUP BY clause, the COUNT(*) will be 1 -- and those are the ones we want.  We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.

So, here's an example, assuming we are comparing tables A and B, and the primary key of both tables is ID:

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...

FROM

(

  SELECT 'Table A' as TableName, A.ID, A.COL1, A.COL2, A.COL3, ...

  FROM A

  UNION ALL

  SELECT 'Table B' as TableName, B.ID, B.COL1, B.COl2, B.COL3, ...

  FROM B

) tmp

GROUP BY ID, COL1, COL2, COL3 ...

HAVING COUNT(*) = 1

ORDER BY ID


The above returns all rows in either table that do not completely match all columns in the other.  In addition, it returns all rows in either table that do not exist in the other table.  It handles nulls as well, since GROUP BY normally consolidates NULL  values together in the same group.  If both tables match completely, no rows are returned at all.

The MIN() aggregate function used on the TableName column is just arbitrary -- it has no effect since we are only returning groups of rows in which there has been no consolidation with the GROUP BY (note the HAVING clause). 

I've posted an implementation of this technique as a stored procedure in the SQLTeam script library section of the forums (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=23054).  Here it is, below:

CREATE PROCEDURE CompareTables(@table1 varchar(100),

 @table2 Varchar(100), @T1ColumnList varchar(1000),

 @T2ColumnList varchar(1000) = '')

AS

 

-- Table1, Table2 are the tables or views to compare.

-- T1ColumnList is the list of columns to compare, from table1.

-- Just list them comma-separated, like in a GROUP BY clause.

-- If T2ColumnList is not specified, it is assumed to be the same

-- as T1ColumnList.  Otherwise, list the columns of Table2 in

-- the same order as the columns in table1 that you wish to compare.

--

-- The result is all rows from either table that do NOT match

-- the other table in all columns specified, along with which table that

-- row is from.

 

declare @SQL varchar(8000);

 

IF @t2ColumnList = '' SET @T2ColumnList = @T1ColumnList

 

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @t1ColumnList +

 ' FROM ' + @Table1 + ' UNION ALL SELECT ''' + @table2 + ''' As TableName, ' +

 @t2ColumnList + ' FROM ' + @Table2

 

set @SQL = 'SELECT Max(TableName) as TableName, ' + @t1ColumnList +

 ' FROM (' + @SQL + ') A GROUP BY ' + @t1ColumnList +

 ' HAVING COUNT(*) = 1'

 

exec ( @SQL)


 

Print | posted on Wednesday, November 10, 2004 9:29 AM | Filed Under [ T-SQL Techniques GROUP BY ]

Feedback

Gravatar

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

Well Jeff

I also had this requirement and What I did is

Select * from (
Select Id,col1,col2...,coln from table1, 'Old'
Union all
Select Id,col1,col2...,coln from table2, 'New'
) T order by Id

Thanks for your new approach
6/13/2005 10:40 AM | Madhivanan
Gravatar

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

Hey,
This really works!
Thanks
7/26/2005 10:40 AM | Preethi
Gravatar

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

how to calculate the percentage?
8/3/2005 11:53 PM | unruledboy
Gravatar

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

>> how to calculate the percentage?

Percentage of what?

To calculate a percentage in general, you divide the (# of things you have) by the (# of things you should have). Then you multiply the result by 100, round to 2 decimal places, and add a little "%" symbol after it.

But I have a feeling that wasn't your question ...

- Jeff
8/4/2005 8:39 AM | Jeff
Gravatar

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

Thanks for your work on this!
8/4/2005 5:27 PM | Dave Bine
Gravatar

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

This is what I was founding!!!
Good job!
Congratulations!
8/10/2005 4:48 AM | xikotet
Gravatar

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

This really helps me. Is there a way where I dont have to pass the column names. I have to compare two tables that are identical. I want the code to get the column names and use it as the column list.
So all I have to do is pass to the stored proc the two table names.
8/12/2005 11:08 AM | Sheetal
Gravatar

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

Dear Jeff. Thanks for your code supply.

It helped me solve a problem.
I have to make some extentions to a database someone else has made. To track down where he updates when I ender a new order I take backup of the database before and after the order is entered. Then I compare all the changes in all the tables.

The code is below.

I have also made a more clean interface for finding all the column names.

Please return to me if You or other readers have suggestions how to optimize this block of code.

--------- code sql script ------------>


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetColumnNamesString]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetColumnNamesString]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


-- TEST SCRIPT --
/*
declare @column_names varchar(4000), @prim_key varchar(50)
exec GetColumnNamesString 'HSDatabase', 'AppVers', @column_names output, @prim_key output
print @prim_key + '->' + @column_names
*/

CREATE PROCEDURE GetColumnNamesString (@database_name varchar(50), @table_name varchar(50), @result varchar(4000) output, @prim_key varchar(50) output)
AS
BEGIN

declare @strSQL varchar(200)
set nocount on
create table #tmp_table (col_names varchar(50))
set @strSQL = 'insert into #tmp_table (col_names) select column_name from ' + @database_name + '.information_schema.columns where Table_name = ''' + @table_name + ''''
exec(@strSQL)
set @result = ''
select @result = @result + CASE WHEN LEN(@result)>0 THEN ', ' ELSE '' END + '[' + col_names + ']' from #tmp_table
set @prim_key = (select top 1 col_names from #tmp_table)
set nocount off
drop table #tmp_table

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CompareTables]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CompareTables]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

-- TEST SCRIPT --
-- CompareTables 'HSDatabase', 'HSOld', 'BatchStatus'

CREATE PROCEDURE CompareTables(@databaseA varchar(50), @databaseB varchar(50), @tableA varchar(50), @tableB varchar(50) = '')
AS


IF @tableB = '' SET @tableB = @tableA

declare @strTmp varchar(50), @col_namesA varchar(4000), @strSQL varchar(8000), @prim_key varchar(50)
exec GetColumnNamesString @databaseA, @tableA, @col_namesA output, @prim_key output
-- print @col_namesA

set @strSQL = 'SELECT min(TableName) as TableName , ' + @col_namesA + '
FROM (
SELECT ''New_' + @tableA + ''' as TableName, A.*
FROM ' + @databaseA + '.dbo.' + @tableA + ' A
UNION ALL
SELECT ''Old_' + @tableB + ''' as TableName, B.*
FROM ' + @databaseB + '.dbo.' + @tableB + ' B
) tmp
GROUP BY ' + @col_namesA + '
HAVING COUNT(*) = 1
ORDER BY ' + @prim_key

exec( @strSQL )
-- print @strSQL

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CompareDatabases]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[CompareDatabases]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


-- TEST SCRIPT --
-- CompareDatabases 'HSDatabase', 'HSOld'
-- select table_name from HSDatabase.information_schema.tables

CREATE PROCEDURE CompareDatabases(@databaseA varchar(50), @databaseB varchar(50))
AS

declare @strSQL varchar(200), @strTmp varchar(50), @nLoop int
set nocount on
create table #tmp_table (table_names varchar(50))
set @strSQL = 'insert into #tmp_table (table_names) select table_name from ' + @databaseA + '.information_schema.tables'
exec(@strSQL)

declare crsr cursor local fast_forward for select * from #tmp_table
set @nLoop = 0
open crsr
fetch next from crsr into @strTmp
while (@@fetch_status=00)
begin
print 'exec CompareTables ''' + @databaseA + ''', ''' + @databaseB + ''', ''' + @strTmp + ''''
exec CompareTables @databaseA, @databaseB, @strTmp
fetch next from crsr into @strTmp
set @nLoop = @nLoop + 1
end -- loop
close crsr
deallocate crsr

set nocount off
drop table #tmp_table

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--------------> end code ---------
8/12/2005 11:20 AM | Thomas Pedersen
Gravatar

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

The only drawback with using Union is that the tables must have the same number of columns and the data types must match.

What about comparing two tables to determine which rows are different when the tables have a different number of columns with a common id?
8/18/2005 6:57 PM | cedar
Gravatar

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

>What about comparing two tables to determine which rows are different when the tables have a different number of columns with a common id?

If the tables have a different number of columns, each row is by definition different, eh? But if you mean compare only the same-named columns...

Use dynamic SQL and populate a variable with all the column names that exist in both tables, then do the same union query. If you then absolutely must, outer join by ID to get the extra non-shared columns.

P.S. use information_schema.columns, not the system tables, as the schema views are guaranteed not to change between SQL Server versions but the system tables are not. Plus, I find them easier to use, anyway.

Erik
8/26/2005 11:06 PM | Erik Eckhardt
Gravatar

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

Thanks!!! This really worked out for me.....
9/1/2005 4:32 PM | Manuel Morales
Gravatar

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

Very useful proc. thanks very much. The only problem was that one of my table names had spaces in (not my fault!) so I had to pass it to the procedure with square brackets around it eg.

dbo.CompareTables '[adviser_Dimension real]','adviser_Dimension', ... yadda yadda yadda
9/7/2005 12:05 AM | Karl
Gravatar

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

Try this,
even faster

Select ID, COL1, COL2, COL3, ...
FROM TABLE1
WHERE NOT EXISTS
(SELECT 1 FROM TABLE2
WHERE TABLE1.ID = TABL2.ID
AND COL1.TABLE1 = COL2.TABLE2
AND COL2.TABLE1 = COL2.TABLE2
AND COL3.TABLE1 = COL3.TABLE2
AND ...
)

The union all is too slow for a large volumes!!
10/11/2005 3:55 PM | click
Gravatar

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

Try this,
even faster

Select ID, COL1, COL2, COL3, ...
FROM TABLE1
WHERE NOT EXISTS
(SELECT 1 FROM TABLE2
WHERE TABLE1.ID = TABL2.ID
AND TABLE1.COL1 = TABLE2.COL1
AND TABLE1.COL2 = TABLE2.COL2
AND TABLE1.COL3 = TABLE2.COL3
AND ...
)

The union all is too slow for a large volume!!
[syntax correction]
10/11/2005 3:59 PM | click
Gravatar

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

Thanks for the suggestion; your code is the typical way to do this in T-SQL.

The problems with that technique are:

1) Only does a 1-direction compare; it doesn't tell you stuff in table2 that doesn't exist in table1, unlike the UNION technique

2) It doesn't handle NULLs in any of the columns, also unlike the UNION technique. If any columns are NULL, the WHERE condition will fail (even if the rows both match perfectly in both tables)

3) The UNION technique also returns all unmatching rows from both tables, so you can see why any rows don't match. The the technique you have presented only lists out rows on 1 table that have no match.

So, I don't know which is faster (didn't test), but it is comparing apples to oranges, since they both return completely different results and the UNION technique gives you much more information from both tables and handles NULL gracefully.

Thanks for the feedback!
- Jeff
10/11/2005 4:24 PM | Jeff
Gravatar

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

- The NULL can be handled with ISNULL(clomun,true value)
- You can do the same thing with this technique what you can with yours:
Here is how:

SELECT MIN(Table_Name) AS Table_Name, ID, COL1, COL2, COL3, COL4,...
FROM
(
SELECT 'Table A' AS Table_Name, ID,COL1,COL2,COL3,COL4
FROM A
WHERE NOT EXISTS
(SELECT 1 FROM B
WHERE A.ID=B.ID
AND isnull(A.COL1,0) = isnull(B.COL1,0)
AND isnull(A.COL2,'*') = isnull(B.COL2,'*')
...)

UNION ALL

SELECT Table B' AS Table_Name, ID,COL1,COL2,COL3,COL4
FROM B
WHERE NOT EXISTS
(SELECT 1 FROM A
WHERE B.ID=A.ID
AND isnull(A.COL1,0) = isnull(B.COL1,0)
AND isnull(A.COL2,'*') = isnull(B.COL2,'*')
...)
) tmp
group by ID,COL1,COL2,COL3,COL4
having count(*) = 1
order by ID

10/11/2005 4:58 PM | click
Gravatar

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

Do you think that code is more efficient than the simple UNION technique?

- Jeff
10/11/2005 5:28 PM | Jeff S
Gravatar

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

You would be surprised, the answer is yes!
10/11/2005 5:37 PM | click
Gravatar

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

I tried both on two tables that have between 4 - 5 million records.

Yours finished in just over minutes, mine was done in less than 7 minutes.

However, I would not right a UNION ALL at all because that where it slows down! keep the comparison simple by only taking one table as the up-to-date table and the other as the out-of-date table and return one set, not two.

Regards
10/11/2005 5:42 PM | click
Gravatar

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

I tried both on two tables that have between 4 - 5 million records.

Yours finished in just over 8 minutes, mine was done in less than 7 minutes.

However, I would not right a UNION ALL at all because that where it slows down! keep the comparison simple by only taking one table as the up-to-date table and the other as the out-of-date table and return one set, not two.

Regards

10/11/2005 5:43 PM | click
Gravatar

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

Sorry for misspellings!
10/11/2005 5:47 PM | click
Gravatar

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

If you want I can send you the snapshots by e-mail. I tested on SQL server Query Analyzer.

10/11/2005 5:57 PM | click
Gravatar

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

I try to do the same thing, however i don't get the code quite.

SELECT MIN(Table_Name) AS Table_Name, ID, COL1, COL2, COL3, COL4,...
FROM
(
SELECT 'Table A' AS Table_Name, ID,COL1,COL2,COL3,COL4
FROM A
WHERE NOT EXISTS
(SELECT 1 FROM B
WHERE A.ID=B.ID
AND isnull(A.COL1,0) = isnull(B.COL1,0)
AND isnull(A.COL2,'*') = isnull(B.COL2,'*')
...)

UNION ALL

SELECT Table B' AS Table_Name, ID,COL1,COL2,COL3,COL4
FROM B
WHERE NOT EXISTS
(SELECT 1 FROM A
WHERE B.ID=A.ID
AND isnull(A.COL1,0) = isnull(B.COL1,0)
AND isnull(A.COL2,'*') = isnull(B.COL2,'*')
...)
) tmp
group by ID,COL1,COL2,COL3,COL4
having count(*) = 1
order by ID


I am not quite sure where I have to put in my tables.
Does this work in ACCESS?

Thanx
hanselmann
10/12/2005 6:10 AM | hanselmann
Gravatar

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

Put the table names where it says A or B as shown below

SELECT MIN(Table_Name) AS Table_Name, ID, COL1, COL2, COL3, COL4,...
FROM
(
SELECT 'Table A' AS Table_Name, ID,COL1,COL2,COL3,COL4
FROM Table_A A
WHERE NOT EXISTS
(SELECT 1 FROM Table_B B
WHERE A.ID=B.ID
AND isnull(A.COL1,0) = isnull(B.COL1,0)
AND isnull(A.COL2,'*') = isnull(B.COL2,'*')
...)

UNION ALL

SELECT 'Table_B' AS Table_Name, ID,COL1,COL2,COL3,COL4
FROM Table_B B
WHERE NOT EXISTS
(SELECT 1 FROM Table_A A
WHERE B.ID=A.ID
AND isnull(A.COL1,0) = isnull(B.COL1,0)
AND isnull(A.COL2,'*') = isnull(B.COL2,'*')
...)
) tmp
group by ID,COL1,COL2,COL3,COL4
having count(*) = 1
order by ID

Yes it works in Access
10/12/2005 12:45 PM | click
Gravatar

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

Like I said before, this is only to demonstrate that this works as well as the UNION ALL. But I don't think you should use it where the UNION ALL returns what you need, because coding this one is quite awkward. However, when you need to return one set only, then by all means use the NOT EXISTS technique, because the UNION ALL won't do the job in a single set compare.
10/12/2005 1:01 PM | click
Gravatar

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

Moreover, the UNION ALL would work for a single set compare, if the table names are removed from the SQL.
10/12/2005 1:30 PM | click
Gravatar

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

This was a butt saver tonight .. thanks
10/19/2005 10:25 PM | brad
Gravatar

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

I've the same problem, I have one table in MS-Access (up2date copy) & the another one is in MSDE(out-of-date copy) both of them have the same structure, I have to compare MS-Access table with MSDE one to get the changes.

The problem which am facing is that I've some table in MS-Access which have two columns, one is key & another one is data column (which is null for all rows), let's say if some null column in MS-Access get changed and user inserted a value "abc" in it, so when detecting change my code didn't get it.

the query which am running is pasted below

SELECT tblAccess.Fname, tblAccess.TypeID, tblMsde.TypeID_new, tblMsde.SoundFNames_id_s
FROM SoundFNames tblMsde INNER JOIN OPENQUERY(SF,'SELECT Fname, TypeID FROM SoundFNames') tblAccess ON tblMsde.TypeID = tblAccess.TypeID COLLATE SQL_Latin1_General_CP1_CI_AS
WHERE ( tblMsde.Fname not like tblAccess.Fname COLLATE SQL_Latin1_General_CP1_CI_AS )
OR ( tblMsde.TypeID not like tblAccess.TypeID COLLATE SQL_Latin1_General_CP1_CI_AS )

the problem is with the Fname column which is of type nvarchar, when i compare null value in MSDE with non-null value in MS-Access then it didn't detect it, don't knw what would i do to get this modification?
10/20/2005 1:30 AM | Qazi Mobeen
Gravatar

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

Qazi Mobeen --

>>don't knw what would i do to get this modification?

You might start by trying the technique I've demonstrated in the article. I'm a little confused why you are posting here if you didn't even read or try the technique I presented; as I state in the article, the UNION ALL technique handles Null comparisons just fine. Why don't you try it?
10/20/2005 8:29 AM | Jeff
Gravatar

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

I was interested in using this technique for detecting change in a data warehouse load. It looked much simpler and elegant than an approach that I was considering. Some of our source systems include TEXT columns. However, when I tested the use of TEXT fields, I find it is not allowed, returning the message "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator." Do you have any suggestions?
10/26/2005 2:26 AM | Gavin
Gravatar

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

Excellent Work
11/2/2005 4:17 AM | Mateen Muhammad
Gravatar

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

Thanks Jeff,

I got my problem solved with the help of your Logic of finding the count(*) to be as 1.
11/3/2005 5:49 AM | Sharath Chandra Thotakura
Gravatar

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

Is there a way to make this work between two different databases on the same server?
11/17/2005 5:09 PM | jj
Gravatar

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

Thanx Jeff,

thats great work....u did the most useful thing.........


thanx once again...............
11/24/2005 2:36 AM | Gnanaskandan
Gravatar

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

Thanks Jeff for the idea.

Is there any work around for Sybase version 12.0? The 'select from ()' command doesn't work with the old version of Sybase.

12/8/2005 11:20 AM | Gade
Gravatar

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

Your code gave me an idea. I modified it to compare 2 table's schemas, not just the data. It will return the differences. Run on your database against 2 tables that are the same. You could modify this to compare tables on different databases by supplying the database name in front of the view name.



DECLARE @TABLEA VARCHAR (1000)
DECLARE @TABLEB VARCHAR (1000)

SET @TABLEA = 'P' -- your first table here
SET @TABLEB = 'PCOPY' -- your second table here

SELECT MIN(TableName) as TableName, COLUMN_NAME, ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_NAME
FROM
(
SELECT @TableA as TableName, COLUMN_NAME, ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE table_name = @tableA
UNION ALL
SELECT @TableB as TableName, COLUMN_NAME, ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS B
WHERE table_name = @tableB
) AS tmp
GROUP BY COLUMN_NAME, ORDINAL_POSITION,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME, COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME, DOMAIN_CATALOG, DOMAIN_NAME
HAVING COUNT(*) = 1
ORDER BY COLUMN_NAME, ORDINAL_POSITION
12/20/2005 6:29 PM | Tom Holden
Gravatar

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

Hi everybody. I used EXCEPT statement to compare two tables, and it worked quite good!
12/28/2005 10:24 AM | Evgeny
Gravatar

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

I keep getting rows from both tables. My tables are the same. If I change one value in one row of one table and run the query I get the changed row and the corsponding row from the other table. what am I doing wrong?
2/7/2006 4:24 PM | John
Gravatar

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

John -- I am not sure what you mean. The point of this technique is to see rows in BOTH tables are different. If you want to only return rows a particular table, then you can either do a LEFT OUTER JOIN or apply a filter to this technique (Having COUNT(*)=1 and MIN(TableName)='TableToShow')

If that doesn't help you out, I'll need a specific example of what you are trying to do.
2/7/2006 5:25 PM | Jeff
Gravatar

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

Thanks for your help, I am new at this.

I can’t get the name of the table to show up correctly.
If I do this: (It sort of works)
SELECT tmp.PC_ID, tmp.ClientNameID, tmp.ClientAFE
FROM [
SELECT 'AA' as TableName, DBO_PROJECTCONTROL.PC_ID, DBO_PROJECTCONTROL.ClientNameID, DBO_PROJECTCONTROL.ClientAFE FROM DBO_PROJECTCONTROL
union all
SELECT 'BB' as TableName, tblPC.PC_ID, tblPC.ClientNameID, tblPC.ClientAFE FROM tblPC
]. AS tmp
GROUP BY tmp.PC_ID, tmp.ClientNameID, tmp.ClientAFE
HAVING Count(*)=1
ORDER BY tmp.PC_ID;

I get this: (No Table Names)
Query9
PC_ID ClientNameID ClientAFE
66 14
66 15

Then If I do this: (Like you have it I think)
SELECT (TableName) as TableName, tmp.PC_ID, tmp.ClientNameID, tmp.ClientAFE
FROM [
SELECT 'AA' as TableName, DBO_PROJECTCONTROL.PC_ID, DBO_PROJECTCONTROL.ClientNameID, DBO_PROJECTCONTROL.ClientAFE FROM DBO_PROJECTCONTROL
union all
SELECT 'BB' as TableName, tblPC.PC_ID, tblPC.ClientNameID, tblPC.ClientAFE FROM tblPC
]. AS tmp
GROUP BY tmp.PC_ID, tmp.ClientNameID, tmp.ClientAFE
HAVING Count(*)=1
ORDER BY tmp.PC_ID;

I get : A circular error on tablename


One last thing, IF I do :
SELECT 'Table' as TableName, tmp.PC_ID, tmp.ClientNameID, tmp.ClientAFE
FROM [
SELECT 'AA' as TableName, DBO_PROJECTCONTROL.PC_ID, DBO_PROJECTCONTROL.ClientNameID, DBO_PROJECTCONTROL.ClientAFE FROM DBO_PROJECTCONTROL
union all
SELECT 'BB' as TableName, tblPC.PC_ID, tblPC.ClientNameID, tblPC.ClientAFE FROM tblPC
]. AS tmp
GROUP BY tmp.PC_ID, tmp.ClientNameID, tmp.ClientAFE
HAVING Count(*)=1
ORDER BY tmp.PC_ID;

I’ll get this:
Query9
TableName PC_ID ClientNameID ClientAFE
Table 66 14
Table 66 15

Think my issue is with the first select (tablename) as tablename.

Once again Thanks a lot for this.
2/8/2006 10:40 AM | John
Gravatar

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

First off -- are you using SQL Server? That syntax with the square brackets isn't valid.

If you using SQL Server, then you need to take the MIN() of table name, as demonstrated and discussed in the blog post. That's the way grouping works -- you must either GROUP BY each column in your select, or aggregate (summary) each column.
2/8/2006 11:15 AM | Jeff
Gravatar

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

I am using Access 2003. The square brackets replace the round ones in the query editor on it's own.
2/8/2006 12:03 PM | John
Gravatar

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

2/14/2006 3:30 PM | Little Tidbits of Random Knowled
Gravatar

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

This is great Jeff,

Is there a way to get (by extending the same logic), to get
1. what is in table A in addition to that in B
2. what is in table A which is different in data except for the primary key in B.

Basically I have 2 tables.
A is source, B is Destination. I need to
1. Insert new records (when compared with A) to B and
2. Update changed (when compared with A) records in B so as to have the same data of A in B as well.


2/16/2006 12:30 PM | Srinika
Gravatar

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

works great, what if I want to have the global sum values of the records found...
I get:
record1, mny1 from table A
record2, mny2 from table B

I want to add the mny column of table A and table B of these records..
2/20/2006 10:38 AM | andres
Gravatar

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

hello,

i need to find two colums in two tables are identical, can anybody help me
2/23/2006 7:34 AM | nidheesh
Gravatar

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

hello friends,

i have two two table, and in which i need to compare two columns, and return if both are same,

that is to check for key relationships

hope a help from u
2/23/2006 7:43 AM | vinoth
Gravatar

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

I tried it, but I got error message "Circular Reference caused by alias "TableName" in query definition's SELECT list.

How can I solve that?

Thanks
3/19/2006 10:12 AM | Steve
Gravatar

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

Hi Everyone,
I was going through the information provided above and attempted to plug in the SQL statements into access and could not get it to work. I have just started using SQL and so I am not familiar with any other options.

Basically I have Billing Table Billing with 500 records and Customer Table with 756 records. I need to have a query that will show me the 256 extra entries on Customer Tables that are not present on Billing Table.

Please help me out. Thanks in advance.

Rumy Deen
3/25/2006 8:42 PM | Rumy Deen
Gravatar

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

What happens if the "column name string" is greater than 1500 characters long? :)

As a side note, I have been able to massage the output from this table into a matrix for determining all three cases that can exhist:

-In target not in source
-In source not in target
-In target and source, but different

The complexity enters when your have a composite primary key :)

-scott
3/27/2006 6:43 PM | Scott
Gravatar

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

Add-on: The fourth case I do not care about...

In target and in source, but same.

P.S. I should add that this method is great and thanks to Jeff for publishing it!

-scott
3/27/2006 6:45 PM | Scott
Gravatar

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

Jeff,

WOW Thanks! That just saved me a bunch of time! I had a question:

How do I know which table a row is from? If two rows are returned, how do you know which row came from which table? Is TableA always first? If only one row is returned, how do you know which table it is from?

Steve:

I got the same error ( "Circular Reference caused by alias "TableName" in query definition's SELECT list) using MS Access. I simply reomved the "MIN(TableName) as TableName," from the beginning of the statement. Hope that helps.

Thanks again Jeff,

Ben
3/29/2006 8:44 PM | Ben
Gravatar

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

NM - My problem was that I got the circular reference error and removed the MIN(TableName) as TableName and it worked, but not seeing the table names tho. So I thought about it and, duh, I put TableName back in, without the min(TableName) as part and it works great!

Thanks again,

Ben
3/30/2006 11:27 AM | Ben
Gravatar

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

Gravatar

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

Credit where credit is due to Jeff.
Well thought out, and well explained.
(Easily built on to.) It is amazing how many examples there are floating around about this subject that miss the fact that there are many tables with nulls out there.
It is great to see people with skill and experience leading by example. It makes the learning process for people like myself much smoother.
4/4/2006 8:02 AM | Synchro
Gravatar

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

Regading: [NULL can be handled with ISNULL(clomun,true value) and ...You can do the same thing with this technique what you can with yours]

I think the biggest issue I have with that is simple data comparision. If I use ISNULL(intCol, 0) or ISNULL(txtCol, '*') then what kind of comparison do I have in the scenario where zero or * is a valid value for the column? I state it like that because you "could" have a 'false positive' scenario or even possibly a mild cartesian (unique indexes can have NULL columns - not everyone uses proper primary keys!). As far as I am concerned, even though the NOT EXISTS method is still set-based, the UNION ALL is certainly more pure and less likely to give a false sense of comparison.
4/6/2006 11:45 AM | David L. Penton
Gravatar

# This method fails if duplicates are present

It is important to be aware that this method fails utterly if there are any duplicates on either side.

If table A contains:
1,2,3
1,2,3
1,2,4

and table B contains:
1,2,4

then this (Jeff's) method will find the tables to be identical, because the group by will result in 2 counts of (1,2,3) and 2 counts of (1,2,4), and no instances of 1 counts.
4/11/2006 9:53 AM | Perry
Gravatar

# How to fix Jeff's method (I think)

I think that Jeff's method can be fixed like so:


SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, COUNT(*) NDUPS, A.ID, A.COL1, A.COL2, A.COL3, ...
GROUP BY ID, COL1, COL2, COL3 ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, COUNT(*) NDUPS, B.ID, B.COL1, B.COl2, B.COL3, ...
GROUP BY ID, COL1, COL2, COL3 ...
FROM B
) tmp
GROUP BY NDUPS, ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID
4/11/2006 9:55 AM | Perry
Gravatar

# Corrected syntax of fixed version

Corrected syntax verifying that the following fixes Jeff's code to handle duplicates correctly (the innermost GROUP BY clauses were on the wrong side of the FROM clauses):


SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, COUNT(*) NDUPS, A.ID, A.COL1, A.COL2, A.COL3, ...
FROM Table1 A
GROUP BY ID, COL1, COL2, COL3 ...
UNION ALL
SELECT 'Table B' as TableName, COUNT(*) NDUPS, B.ID, B.COL1, B.COl2, B.COL3, ...
FROM Table2 B
GROUP BY ID, COL1, COL2, COL3 ...
) tmp
GROUP BY NDUPS, ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
ORDER BY ID


(Apparently this forum strips out spaces, which is very sad.)
4/11/2006 10:07 AM | Perry
Gravatar

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

Need a little help with this guys. I have a similar issue.

I have two tables that are exact. One is my Live Database and one is the back up that is backedup every friday.

There are 10 fields in each table. I want to compare the live DB to the back up to see what records have been change and to what.

I used the original script and i did find the records in there that have changed. But thats all I want, not all the others.

Any Ideas
4/13/2006 2:23 PM | Rock
Gravatar

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

Hi guys, I am trying to do the following.

I have tables called table1 and table2.

On table1 i ahve a lsit of names in the colunm "name" and have verify or compare which names are present in table2.

What I need as a result is a new table called table3, where i can see the name (name of customer, for example) that is present in both. What will be the best way to do it. Simpler the better, I am not a programmer or DBA.

THank you to all, great info on this forum.

Best wishes.
4/13/2006 5:05 PM | Tomas CR
Gravatar

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

How would I alter this so it union/groups on two fields (ex: phone_number, date); but also filters with the following:

+/- 0.5 margin on field (total_time)
+/- 1 minute on field (call_time)

Does that make sense? How would I code that?

Thanks for your help!
5/1/2006 12:57 PM | Nick
Gravatar

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

Hi,
I think that this might work too ! Can anybody Comment ?
SELECT COALESCE(A.TableName,B.TableName)
,COALESCE(A.ID,B.ID)
,COALESCE(A.COL1,B.COL1)
,COALESCE(A.COL2,B.COL2)
,COALESCE(A.COL3,B.COL3)
.....
FROM ( SELECT 'Table A' as TableName, ID, COL1, COL2, COL3, ...
FROM TABLE_A1 ) AS A
FULL OUTER JOIN
( SELECT 'Table B' as TableName, ID, COL1, COL2, COL3, ...
FROM TABLE_b1 ) AS B
ON A.ID = B.ID
WHERE (A.ID IS NULL) OR (B.ID IS NULL)
5/3/2006 6:35 AM | SauPa
Gravatar

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

<QUOTE>
It is important to be aware that this method fails utterly if there are any duplicates on either side.

If table A contains:
1,2,3
1,2,3
1,2,4

and table B contains:
1,2,4

then this (Jeff's) method will find the tables to be identical, because the group by will result in 2 counts of (1,2,3) and 2 counts of (1,2,4), and no instances of 1 counts.
</QUOTE>

thanks, my situation calls for this validation as well since none of my tables have and ID column (don't ask...). however, i believe the original solution assumed that there was an ID field included in the list of fields. this field would automatically make it impossible for the above scenario to occur since every row in table would be unique, ie. table A could never contain two instances of 1,2,3.
5/3/2006 11:42 AM | TKO
Gravatar

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

hi,
i have to check 2 tables and compare them. but no of records are huge (5-6 million) and time constraint is quite high..
can i have help on stored procedures in this regard..

thanks..
5/10/2006 6:55 AM | sanjay
Gravatar

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

CHEERS DUDE, saved a lot of hassle,
got it to work in Access to as below.
'Unit No' was my pri key and just used
that to test on and it worked.

SELECT [Unit No]
FROM
(
SELECT A.[Unit No]
FROM [Accredited Units] A
UNION ALL
SELECT B.[Unit No]
FROM [Unit Description - master] B
) tmp
GROUP BY [Unit No]
HAVING COUNT(*)=1
ORDER BY [Unit No];

regards
rob.
5/26/2006 12:19 AM | Robert Marshall
Gravatar

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

This was a very helpful start to my being able to compare Engineering Bills of Material to Production Build Transactions.

THANKS
6/7/2006 6:41 PM | Wyatt
Gravatar

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

Hello Jeff,

Thank you for posting this. I implemented a version of your procedure in PL/SQL. I modified the query such that it will handle duplicate rows. For example, if there are two instances of a given row in tableA and three instances of a given row in tableB.

SELECT counter, max(tablename), {column list}
FROM (SELECT COUNT (*) AS counter, 'tableA' AS tablename,{column list}
FROM tableA
GROUP BY {column list}
UNION ALL
SELECT COUNT (*) AS counter, 'tableB' AS tablename,{column list}
FROM tableB
GROUP BY {column list})
GROUP BY counter, {column list}
HAVING COUNT (*) <> 2;
6/21/2006 2:12 PM | Matt Edelman
Gravatar

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

Hi guys. I have a problem I've been struggling with for a while now. I have two tables (say table1 and table2). Both have a column named 'filename'. 'table1' could have a value in the 'filename' column, say 'file2' and 'table2' would have 'file1,file2,file3' in it's column. So what I was trying to find out how I could do something like this:
SELECT * FROM table1, table2 WHERE table2.filename LIKE '%table1.filename%';
6/26/2006 4:51 AM | DS
Gravatar

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

3/27/2006 6:43 PM Scott says:
As a side note, I have been able to massage the output from this table into a matrix for determining all three cases that can exist:
-In target not in source
-In source not in target
-In target and source, but different

This is something I have been trying to achieve. Would you like to share your code with us.

Thanks
7/4/2006 8:04 AM | SteveR
Gravatar

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

IT REALLY HELPED ME A LOT. THANKS A LOT
7/7/2006 2:49 AM | srikanth
Gravatar

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

Bravo and well done sir! This is GIGANTICALLY helpful. Many thanks.
7/13/2006 11:14 AM | Adam
Gravatar

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

You helped me out tremendously!! Go boy! Thanks from the bottom of my heart
7/19/2006 3:02 PM | Abhi
Gravatar

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

Hello, i tried UNION and it wors fine.

But folks.. take a look on this. :D

select * from tableA (select it to the text file and save)
select * from tableB (select it to the text file and save)

run TotalCommander, file-compare by content

1. Much faster - SQL command completed the fastest way
2. No need to chceck script for bugs, no parameters
3. Totalcommander shows you where you have differences.

Of course, if you need a dump of differences, you can use script. But in my case, i need to be just sure, that 2 tables are identical. :-)

lame solution, but fast and reliable..for some Cases :D
7/26/2006 8:25 AM | Duchy
Gravatar

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

Vau, it is greate!!! Shoter and faster. Hmmm, you are big head, Duchy.......
:-)
7/26/2006 9:04 AM | Zlosyn (bad kid)
Gravatar

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

Rob:

SELECT
A.[Unit No],
B.[Unit No]
FROM
[Accredited Units] A
FULL OUTER JOIN [Unit Description - master] B
WHERE
A.[Unit No] IS NULL
OR B.[Unit No] IS NULL

this will probably be faster, and the result gives you the info you need in *both* directions, whether the missing row is in table A or B, depending on which value is NULL.
8/4/2006 8:46 PM | Erik Eckhardt
Gravatar

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

Hey al .. i found this while digging ....
can i know which col is been updated if i have base table as A ...

is it possible in Tsql
8/24/2006 6:42 AM | theBug
Gravatar

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

Thank's a lot
This really help me to compare two tables

Nice Works
9/4/2006 8:38 AM | Agus
Gravatar

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

Is this really the fastest way???? I have always used the following as and it is easy to use the two sets of results to update a version.

--Client2 is new table client1 is old version

--Inserted Updated records
SELECT a.id, a.BranchCode, a.ClientCode, a.LastName, a.FirstName, a.Pref_Vendor, a.Pref_Segment, a.Pref_FrequentTrav, a.Pref_Notes
FROM client2 a left join client1 b on
a.id = b.id
and isnull(a.BranchCode,'') = isnull(b.BranchCode,'')
and isnull(a.ClientCode,'') = isnull(b.ClientCode,'')
and isnull(a.LastName,'') = isnull(b.LastName,'')
and isnull(a.FirstName,'') = isnull(b.FirstName,'')
and isnull(a.Pref_Vendor,'') = isnull(b.Pref_Vendor,'')
and isnull(a.Pref_Segment,'') = isnull( b.Pref_Segment,'')
and isnull(a.Pref_FrequentTrav,'') = isnull(b.Pref_FrequentTrav,'')
and isnull(a.Pref_Notes,'') = isnull(b.Pref_Notes,'')
where b.id is null

--Deleted records
SELECT a.id, a.BranchCode, a.ClientCode, a.LastName, a.FirstName, a.Pref_Vendor, a.Pref_Segment, a.Pref_FrequentTrav, a.Pref_Notes
FROM client1 a left join client2 b on
a.id = b.id
where b.id is null


9/8/2006 12:13 AM | Julian
Gravatar

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

Cheers....... it was really useful..... Thanks...
Sridhar
5/18/2007 8:05 AM | Sridhar
Gravatar

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

First of all. I would like to Thanks Jeff for heck of a code. It really helped me a lot.

I ran into a problem though. I was comparing two tables , per say Table A and Table B. one of the columns had a null value in Table A and Table B had an empty string.

using Jeff's code It was returning the row as different bcuz one of the field value is null in Table A but an empty string in Table B.

My question is, is there a way I could tell to ignore nulls and empty string while comparing.

any help would be greatly appreciated.

Thanks,

KG
6/11/2007 4:48 AM | KG
Gravatar

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

just use ISNULL() or COALESCE() to replace NULL values with empty strings.

i.e.,

isnull(somecolumn,'')

or

coalesce(somecolumn,'')
6/11/2007 8:26 AM | Jeff
Gravatar

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

That works perfect. But I define these columns in a variable. Is there a way I can achieve it using variables.

Appreciate your help and your prompt reply.

Thanks,

KG
6/11/2007 1:59 PM | KG
Gravatar

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

Nevermind. Its working.

Thanks alot Jeff
6/11/2007 2:07 PM | KG
Gravatar

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

This code really helped me , got me out of a difficult sittuation !!
Thank you
6/25/2007 1:41 AM | shalina
Gravatar

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

Hi,

it is possible to compare columns of the type text?

Thanks,
JCV
6/25/2007 4:25 PM | Juan Carlos
Gravatar

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

Hi Jeff, hi all others ;-)
Thanks for the code.

It change the code to be more handy
- compare two tables from different databases on one server
- get the column names from the tables
- option to skip a Column (different IDs but same data rows)

The code is below.
-- SQL CODE --
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE SP_CompareTables(
@database1 varchar(100), @table1 varchar(100),
@database2 varchar(100), @table2 Varchar(100), @skipthisCol Varchar(100)
)

AS

-- Table1, Table2 are the tables or views to compare.
--
-- The result is all rows from either table that do NOT match
-- the other table in all columns specified, along with which table that
-- row is from.

BEGIN

DECLARE
@RC int,
@SQL varchar(8000),
@columns_table1 varchar(8000),
@columns_table2 varchar(8000)

SET RC = 1
IF(@database1 = '' OR @database2 = '' OR @table1 = '' OR @table2 = '' OR
@database1 IS NULL
OR @database2 IS NULL OR @table1 IS NULL OR @table2 IS NULL ) BEGIN
PRINT 'ERROR MISSING PARAMETERS'
RETURN
END


PRINT '---------------- START ----------------'

SELECT @sql = 'SELECT column_name FROM ' + @database1 + '.INFORMATION_SCHEMA.Columns where TABLE_NAME = ''' + @table1 + ''''


create table #t (columnx varchar(8000)) insert into #t exec (@sql)

SELECT @columns_table1 = (CASE WHEN t.columnx != @skipthisCol THEN
COALESCE(@columns_table1 + ',', '') + t.columnx ELSE '' END)

FROM ( select * from #t ) as t

delete from #t

-- remove comma if @skipthisCol
IF SUBSTRING(@columns_table1, 1,1) = ','
BEGIN
SET @columns_table1 = SUBSTRING(@columns_table1, 2, LEN(@columns_table1)-1);
END


SELECT @sql = 'SELECT column_name FROM ' + @database2 + '.
INFORMATION_SCHEMA.Columns where TABLE_NAME = ''' + @table2 + ''''


INSERT INTO #t exec (@sql)

SELECT @columns_table2 = (CASE WHEN t.columnx != @skipthisCol THEN
COALESCE(@columns_table2 + ',', '') + t.columnx ELSE '' END)

FROM ( SELECT * FROM #t ) as t

IF(SUBSTRING(@columns_table2, 1,1) = ',')
BEGIN
SET @columns_table2 = SUBSTRING(@columns_table2, 2,
LEN(@columns_table2)-1)
END

IF @columns_table2 = ''
SET @columns_table2 = @columns_table1
IF @columns_table2 != @columns_table1
BEGIN
SELECT 'TABLE COLUMNS ARE DIFFERENT' as ERROR,
@columns_table1 as Columns_of_Table1 , @columns_table2 as
Columns_of_Table2
RETURN
END


SET @SQL = 'SELECT ''' + @database1 + '.dbo. ' + @table1 + ''' AS TableName, ' + @columns_table1 + ' FROM ' + @database1 + '.dbo.' + @Table1 + ' UNION ALL SELECT ''' +
@database2 + '.dbo. ' +@table2 + ''' As TableName, ' +

@columns_table2 + ' FROM ' + @database2 + '.dbo.' + @Table2

SET @SQL = 'SELECT Max(TableName) as TableName, ' + @columns_table1 + ' FROM (' + @SQL + ') A GROUP BY ' + @columns_table1 + ' HAVING COUNT(*) = 1'

EXEC (@SQL)
SET @RC = @@ROWCOUNT

PRINT 'SQL: ' + @SQL


PRINT @RC
IF(@SQL IS NOT NULL) -- no error bilding @sql statement
BEGIN
IF(@RC = 0)
SELECT 'ALL EQUAL ' + @database1 + '.dbo.' + @Table1 +' und ' +
@database2 + '.dbo.' + @Table2 as RESULT
ELSE SELECT cast(@RC as varchar) + ' DIFFENCES FOUND BETWEEN' +
@database1 + '.dbo.' + @Table1 +' und ' + @database2 + '.dbo.' + @Table2 as RESULT
END

END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Please return to me if You or other readers have suggestions how to optimize this block of code.
7/18/2007 6:06 AM | Guido
Gravatar

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

Hi,

Your code looks pretty good and I would like to use it, but my situation is a litle bit different from any I have read about. Basically, I have 2 databases say X and Y in phpMyAdmin and I want to see if a given customer ID is present in both X and Y. X has all our existing customers, but as for the new customers, they are in both X and Y. How would I do the comparison? If an ID is in X, but not in Y then I want to add a paricular form field to the Account of that customer.

Thanks,
percy
7/28/2007 12:47 AM | percy Thaba
Gravatar

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

sorry, i just realized i had skipped over the post right above mine........which is what i think i need. i will use this and see if it helps me.

THanks
7/28/2007 1:15 AM | percy
Gravatar

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

I am working on an Access application where I have to synchronize tables. An adaptation of this is useful for whittling down duplicate data between two tables with the same schema.

DELETE *
FROM TableToDeleteFrom AS a
WHERE EXISTS
(SELECT 1 FROM TalbeToRead Against AS b
WHERE a.Key1 = b.Key1
AND a.Key2 = b.Key2
AND a.Key3 = b.Key3
AND ...
)

ROCK ON!
8/10/2007 11:48 AM | Chuck
Gravatar

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

Thanks for the code...
it helped me a lot...
8/28/2007 8:53 AM | Carolin
Gravatar

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

Hi Jeff, I tried the Union Query but, I am not sure if I am using this for the right purpose.
I have 2 tables in Microsoft Access that have the exact same fields/colums and rows, but the actual data in the customer records may change. What I want to know is what data changes within each record.
Can someone assist with this?
9/27/2007 9:13 AM | Dorneika
Gravatar

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

Wow, this is an awesome find! This can get rid of all my VB code to do this the hard way.

I am having one problem though.

I have two stored procedures. One shows me what is to be deleted, and then one to show me what to update. My theory is to delete the rows from the master table that no longer match, and then insert all the new records, basically creating a duplicate table (with the added bonus of an extra Flag in my Master table to tell me what rows were updated for processing later). My problem is that once I run the procedure to show me the records to be deleted, how do I actually delete those selected records from the Master table?

Here's my code:
-------------------------------------
ALTER PROCEDURE dbo.ShowDeletes_SiteLevel
AS EXECUTE ShowDeletes SiteLevel_Master, SiteLevel,
'site_no, panel_id, site_name, site_addr1, site_addr2, sitestat_id, city_name, state_id, zip_code, change_date, change_type, sitetype_id, branch_no, timezone_no, phone1, ext1, phone2, ext2, servco_no'
-------------------------------------
-- I have this as a separate function so I can run it on multiple tables.
ALTER PROCEDURE dbo.ShowDeletes
(@table1 varchar(100), @table2 Varchar(100), @tColumnList varchar(1000))
AS
declare @SQL varchar(8000);

set @SQL = 'SELECT ''' + @table1 + ''' AS TableName, ' + @tColumnList + ' FROM ' + @Table1 + ' UNION SELECT ''' + @table2 + ''' As TableName, ' + @tColumnList + ' FROM ' + @table2
set @SQL = 'SELECT ' + @tColumnList + ' FROM (SELECT Max(TableName) as TableName, ' + @tColumnList + ' FROM (' + @SQL + ') A GROUP BY ' + @tColumnList + ' HAVING COUNT(*) = 1) B WHERE TableName = ''' + @table1 + ''''

exec ( @SQL )
-------------------------------------

So, my two commands to execute the deletions and additions are:

-- DELETE CS_No_Level_Master EXEC ShowDeletes_CS_No_Level
This does not work! I can't get the syntax right. Please help!

However,
-- INSERT INTO CS_No_Level_Master EXEC ShowUpdates_CS_No_Level
This WORKS!!! It will insert the rows that need to be updated. (ShowUpdates is the opposite of ShowDeletes, of course, which I did not post but is almost identical anyway)

I'm so close! I know it!
9/28/2007 5:11 PM | Jon
Gravatar

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

Jon -- You should never use a generic "utility" stored procedure like the one I wrote in place of a good, clean, efficient and specific SQL statement. The compare two tables is for quick and dirty ad-hoc things like comparing prod to staging or tying out reports or varying SQL statements.

If you need help writing sql to do what you are asking, you should visit the SQLTeam forums and present your situation there. that is a much better way to ask follow ups and to communicate and get others involved so that you have the best possible solution.
10/1/2007 8:43 AM | Jeff
Gravatar

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

Old thread now but I stumbled across it googling for my requirement for the quickest way to merely see if 2 tables are different. If they are I drop the old table and rename the new table to the Old table name, thereby replacing it. I therefore don't care which records have changed (The table is a result of a pretty complex multi-join with live data that may or may not affect my core resultset). If there are no changes I can also bypass the subsequent trigger that causes other tables based on this table to need to recalculate and asp.net caches to flush so it's a worthwhile optimisation.

Using the above methods seems overkill for my case as I want to exit with a false as soon as the first non match is found. I am considering writing a c# Stored Proc that will just do 'Select * From Table1' each table into 2 datasets (ordered so records most likely to change come first) I can compare using an outer record loop with an inner field loop and exit on any false.

OR is there a more efficient TSQL way?

Cheers
10/3/2007 6:41 PM | HappyTalk
Gravatar

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

OK Well here's what I did in TSQL What I'm unsure about is how the SQL engine process it i.e if the ORDER BY's (that select the events most likely to have changed) are effective. Any optimisations anyone?

DECLARE @Today datetime
SET @Today = dbo.DateOnly(GETDATE())
DECLARE @Abort int
SET @Abort = 0

SELECT TOP(1) *
FROM
(
SELECT TOP (100) PERCENT UserNumber, OutTime, InTime, Timetype
FROM UserTimesNew UN2
WHERE UN2.WorkDate = @Today
ORDER BY UN2.OutTime DESC, UN2.InTime DESC
)UN
FULL OUTER JOIN
(
SELECT TOP (100) PERCENT UserNumber, OutTime, InTime, Timetype
FROM UserTimes U2
WHERE U2.WorkDate = @Today
ORDER BY U2.OutTime DESC, U2.InTime DESC
)U
ON UN.UserNumber = U.UserNumber
WHERE UN.OutTime <> U.OutTime OR
UN.InTime <> U.InTime OR
UN.Timetype <> U.Timetype

IF (@@RowCount = 0)
SET @Abort = 1
10/4/2007 6:22 AM | HappyTalk
Gravatar

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

Hi,

I am trying to adapt this query to compare oracle tables from different environments (dev, qa, staging, production) using the link tables in access. Some of these tables have over 300 columns. Is there a dynamic way to get the column headings as there a quite a few tables to compare and setting up a query for each one would be quite painful.

regards,

Enigma
10/11/2007 9:50 PM | enigmacmpr
Gravatar

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

ok here's the problem im facing...

i have the following tables b1(ContactName,CardBin,PartnerNum), b2(Partnernum, CardBin). I want to check if the cardbin of b2 is the same with b1's for every PartnerNum that exists in both b1,b2 and if it is the same to post a message like"right cardbin asigned'. So the result set would be smthg like that (ContacName,CardBin,PartnerNum,MSG). Attention!!!! B1 may have more elements than b2 and vice i versa...HELP Please....
10/19/2007 5:48 AM | nkthegreek
Gravatar

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

How would I put the results of this query into an temp table for example?
7/10/2008 4:28 AM | JL
Gravatar

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

Tq so much. it help me a lot to solve my prob
7/14/2008 9:01 PM | raziems
Gravatar

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

wow. nice. Can I repost this elsewhere (the-lounge.us I.T. page) with attribution, with a link to this page and proper name credit?

zoid@the-lounge.us
8/6/2008 10:44 AM | Zoid
Gravatar

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

Zoid -- sure!
8/6/2008 10:48 AM | jeff
Gravatar

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

Found the earlier error.

SELECT Min(theTable) as theTable, TableName, TableName, NumberRows FROM
(
Select 'FapwarSourceDetails' as theTable, A.TableName, A.NumberRows
FROM BagheeraSourceDetails A
UNION ALL
SELECT 'FapwarTargetDetails' as theTable, B.TableName, B.NumberRows
FROM BagheeraTargetDetails B
) tmp
GROUP BY TableName, NumberRows
HAVING COUNT(*) = 1
ORDER BY TableName

Changed as above and seems to be working okay.
8/12/2008 10:44 PM | Paul
Gravatar

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

Thanx
8/20/2008 1:16 PM | Sohbet
Gravatar

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

u have made my job easier ,thank you
9/14/2008 5:04 AM | udaya
Gravatar

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

Hi Jeff,

I'm a real SQL Numpty, but I've been given the job of comparing data in 2 tables so I was really pleased to find your SQL Statement above.

I've modified it for my tables and saved it as compare_tables.sql.

Running the sql in sqlplus I get.....

NLT> @compare_tables
77
NLT>

Which is a bit disappointing as I was expecting trumpets, fireworks and all the differences between my 2 tables.

Any idea WHAT I'M DOING WRONG?

cheers

Laurie
9/16/2008 9:30 AM | Laurie Clarke
Gravatar

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

A better way than listed int he article , as it assumes the lists are themselves distinct. which they not nesscesarily.
Comparing 2 lists of id´s ListA, ListB

select id
from
(
select id
from ListA
UNION ALL
select id
from ListB
) tmp
group by id
having count(1)<>2
-- this returns the mismatch as each id should occur exactly twice
9/24/2008 7:09 AM | dba
Gravatar

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

Hi I'm trying to compare two query's of the same tabels
I thought it should be the same then comparing to two Tabels

what I want to find out is how a cluster of People has changed on different times
-witch people are at bot time in the cluster
-witch left the cluster
-witch joint it

my code looks like this:

SELECT MIN(TableName) as TableName, friendly_name, id, owner_id, runtime,
FROM
(SELECT u0.friendly_name, cg0.id, cg0.owner_id, cg0.runtime
FROM cached_graphs AS cg0, clusters AS c0, cluster_contacts AS cc0, users AS u0
WHERE cg0.id=c0.parent_graph_id AND c0.id=cc0.cluster_id AND cc0.contact_id=u0.id AND cg0.runtime ='2008.11.02' AND c0.id=3734

UNION ALL

SELECT u1.friendly_name, cg1.id, cg1.owner_id, cg1.runtime,
FROM cached_graphs AS cg1, clusters AS c1, cluster_contacts AS cc1, users AS u1
WHERE cg1.id=c1.parent_graph_id AND c1.id=cc1.cluster_id AND cc1.contact_id=u1.id AND cg1.runtime ='2008.11.02' AND AND c1.id=3709
) tmp
GROUP BY friendly_name, id, owner_id, runtime,
HAVING COUNT (*)=1
ORDER BY friendly_name

if just UNION the queries there will be something like this:
+------------------------------+------+----------+---------------------+
| friendly_name | id | owner_id | runtime |
+------------------------------+------+----------+---------------------+
| name1 | 3734 | 1 | 2008-11-02 00:00:00 |
| name2 | 3734 | 1 | 2008-11-02 00:00:00 |
| name3 | 3734 | 1 | 2008-11-02 00:00:00 |
| name4 | 3734 | 1 | 2008-11-02 00:00:00 |
| name5 | 3734 | 1 | 2008-11-02 00:00:00 |
| name1 | 3709 | 1 | 2008-11-03 00:00:00 |
| name2 | 3709 | 1 | 2008-11-03 00:00:00 |
| name5 | 3709 | 1 | 2008-11-03 00:00:00 |
+------------------------------+------+----------+---------------------+

cheers

Markus
11/6/2008 12:53 PM | Markus
Gravatar

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

I've got it was just to tired yesterday.
I made 3 queries for each question.

Compare Clusters by two given Cluster ID's (c.id=3709=old OR c.id=3734=new):

-show just the ones who in in both clusters with the information of the new one:

SELECT tmp.id, tmp.friendly_name, cc.cluster_id,cg.owner_id, cg.runtime, cg.id, cg.modularity
FROM cluster_contacts AS cc, clusters AS c, cached_graphs AS cg,
(
SELECT*
FROM
(
SELECT u0.id, u0.friendly_name
FROM cached_graphs AS cg0, clusters AS c0, cluster_contacts AS cc0, users AS u0
WHERE cg0.id=c0.parent_graph_id AND c0.id=cc0.cluster_id AND cc0.contact_id=u0.id AND (cg0.runtime ='2008.11.02' OR '2008.11.03') AND cg0.owner_id=1 AND c0.id=3734
UNION ALL
SELECT u1.id, u1.friendly_name
FROM cached_graphs AS cg1, clusters AS c1, cluster_contacts AS cc1, users AS u1
WHERE cg1.id=c1.parent_graph_id AND c1.id=cc1.cluster_id AND cc1.contact_id=u1.id AND (cg1.runtime ='2008.11.02' OR '2008.11.03') AND cg1.owner_id=1 AND c1.id=3709)tmp
GROUP BY tmp.id
HAVING COUNT(*) = 2
)tmp
WHERE cc.contact_id=tmp.id AND c.id=cc.cluster_id AND cg.id=c.parent_graph_id AND c.id=3734
GROUP BY tmp.id

-show just the ones who left the new one:

SELECT tmp.id, tmp.friendly_name, cc.cluster_id, c.parent_graph_id,cg.owner_id, cg.runtime, cg.modularity
FROM cluster_contacts AS cc, clusters AS c, cached_graphs AS cg,
(
SELECT u0.id, u0.friendly_name
FROM cached_graphs AS cg0, clusters AS c0, cluster_contacts AS cc0, users AS u0
WHERE cg0.id=c0.parent_graph_id AND c0.id=cc0.cluster_id AND cc0.contact_id=u0.id AND c0.id=3734
UNION ALL
SELECT u1.id, u1.friendly_name
FROM cached_graphs AS cg1, clusters AS c1, cluster_contacts AS cc1, users AS u1
WHERE cg1.id=c1.parent_graph_id AND c1.id=cc1.cluster_id AND cc1.contact_id=u1.id AND c1.id=3709
)tmp
WHERE cc.contact_id=tmp.id AND c.id=cc.cluster_id AND cg.id=c.parent_graph_id AND c.id=3709
GROUP BY tmp.id
HAVING COUNT(*) = 1
ORDER BY tmp.id

-show just the ones who new in the new one:

SELECT tmp.id, tmp.friendly_name, cc.cluster_id, c.parent_graph_id,cg.owner_id, cg.runtime, cg.modularity
FROM cluster_contacts AS cc, clusters AS c, cached_graphs AS cg,
(
SELECT u0.id, u0.friendly_name
FROM cached_graphs AS cg0, clusters AS c0, cluster_contacts AS cc0, users AS u0
WHERE cg0.id=c0.parent_graph_id AND c0.id=cc0.cluster_id AND cc0.contact_id=u0.id AND c0.id=3734
UNION ALL
SELECT u1.id, u1.friendly_name
FROM cached_graphs AS cg1, clusters AS c1, cluster_contacts AS cc1, users AS u1
WHERE cg1.id=c1.parent_graph_id AND c1.id=cc1.cluster_id AND cc1.contact_id=u1.id AND c1.id=3709
)tmp
WHERE cc.contact_id=tmp.id AND c.id=cc.cluster_id AND cg.id=c.parent_graph_id AND c.id=3734
GROUP BY tmp.id
HAVING COUNT(*) = 1
ORDER BY tmp.id

Markus
11/7/2008 6:18 AM | Markus
Gravatar

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

Excellent article.....really saved my time and money. Hatts off...
12/7/2008 11:17 AM | Richard
Gravatar

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

Hi Friends,

I am new to Sybase ..I need to compare two table in Sybase database, for example i got two databases and got the tables in these two .. need to compare column to column and show diff.. i want in plan SQL only not on procedures or plsqls .. please help me
12/15/2008 2:49 PM | Mady
Gravatar

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

In most of above queries i am bale to see u techs using <DBname>.information_schema.columns where can i see this in my SQl server i am nota able to do select as shown below


select top 1 column_name from <DBname>.information_schema.columns where Table_name = <tableName>
1/6/2009 11:15 PM | Chinna
Gravatar

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

thank you!
2/17/2009 9:29 AM | Igor D.
Gravatar

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

SELECT MIN(TableName) as TableName, ....
FROM
( .......

Can anyone explain what this statement means exactly ? I´m not so familiar with SQL and I´m trying to compare two tables to check if both have same data. Name of table1=names1, table2=names2.
I don´t understand which tablename to use here. Shoukd I give some imaginary name to the temporary table that is created with UNION ?
I have tried all possible ways (exept the right one :-(

Would be great if someone could write an example with real tablenames-like table1= roses table2= lilies and so on.
It would be much easier to understand how everything hangs together.



2/27/2009 8:39 PM | Milla
Gravatar

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

Great query Jeff..
But i have to agree with click. The UNION ALL takes too much time for querying unlike the use of NOT EXIST.
Thanks for the idea though. =)

Benito
3/5/2009 1:10 AM | Benito
Gravatar

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

Jeff - Thanks for the great work.

Is there any way around the Sybase limit of 31 columns allowed in the Group By clause? I'm trying to compare some bigger tables, some of them have as many as 200 columns to compare. Thanks.
3/11/2009 1:57 PM | Kevin
Gravatar

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

Hi....

Your method fails when the tables have repeated registers.

When you compare:

Table A.Column A
0000007500078820090407164644000001000083139797 <-repeated
0000007500078820090407164644000001000083139797 <-repeated
0000007500078820090407164644000001000083139797 <-repeated
0000007500078820090407164644000001000083139797 <-repeated
0000007500078820090407164644000001000083139797 <-repeated
0000007500078820090407164644000001000083139797 <-repeated
0000007500078920090407174944000001000091980022 <-distinct

vs

Table B.Column A
0000007500078920090407174944000001000091980022 <-distinct, but the same than Table A.Column A

You'll get OK.
4/14/2009 6:26 PM | cesar santis
Gravatar

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

I found it is a lot easier to use a tool like SQLMerger to do this for you. The results are displayed in a nicer way and you can go on with whatever you need to do with the data thereafter easily.

www.auisoft.com/SQLMerger <= the tool that makes it easy to compare data

example on comparing two tables: http://auisoft.com/SQLMerger/How-to/visualize-differences-in-2-databases/

5/5/2009 2:50 AM | Poul Jørgensen
Gravatar

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

Hi, can someone help me out with writing a code to compare 2 tables and carrying over the comments with values that haven't changed. I'm using access. I have 2 tables: one with a column (field) that has archived comments and another table that has new records and old records but empty comments. What i would like to do is compare the ID, and 3 columns (A,B, and C); if they all match (meaning the values in the first table match the values in the second table in the 3 columns) then i would like to take(carry over) the comments in the second table and put them in the new table that has both old and new records.
Thanks!
5/29/2009 6:05 PM | ML
Gravatar

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

Sql Server | Compare 2 tables columns


Code here:
-------------

DECLARE @Db1 NVARCHAR(MAX)
DECLARE @Table1 NVARCHAR(MAX)
DECLARE @Db2 NVARCHAR(MAX)
DECLARE @Table2 NVARCHAR(MAX)
DECLARE @Sql NVARCHAR(MAX)

SET @Db1 = 'MMIS_SNAP'
SET @Table1 = 'CodeTablesData'
SET @Db2 = 'MMIS_SNAP'
SET @Table2 = 'CodeTables'
SET @Sql = ' ' +
' SELECT ''in ' + @Db1 + '.' + @Table1 + ' --- not in ' + @Db2 + '.' + @Table2 + ''' AS TITLE, a.TABLE_CATALOG, a.column_name ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE a.column_name NOT IN (SELECT column_name ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table2 + ''')) ' +
' AND a.table_name IN (''' + @Table1 + ''') ' +

' UNION ALL ' +

' SELECT ''in ' + @Db2 + '.' + @Table2 + ' --- not in ' + @Db1 + '.' + @Table1 + ''' AS TITLE, a.TABLE_CATALOG, a.column_name ' +
' FROM ' + @Db2 + '.INFORMATION_SCHEMA.COLUMNS a ' +
' WHERE a.column_name NOT IN (SELECT column_name ' +
' FROM ' + @Db1 + '.INFORMATION_SCHEMA.COLUMNS b ' +
' WHERE b.table_name IN (''' + @Table1 + ''')) ' +
' AND a.table_name IN (''' + @Table2 + ''') ' +
''

EXEC (@Sql)
Gravatar

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

This is great code...
What I need is to be able to archive all the changes with a timestamp in a 3 table while making the changes (update/insert/delete) to the exiting base table. Can some one help?
6/8/2009 7:09 PM | nick
Gravatar

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

Jeff,
This works well for my task of comparing my Today table with my Yesterday table, where i can have changes to data within a row, or entire new rows in the Today table. However, I specifically do not want to see rows from the Yesterday table which have been modified in the Today table, so for example if the Today and Yesterday tables are identical and i change one element on one row on the Today table, all I want to see is that row returned.

Is the best way to do this to wrap the whole of your solution within a select statement which filters out any rows where TableName = 'Yesterday'?

cheers

Pete
7/1/2009 12:23 PM | Peter Green
Gravatar

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

Hi
I have to tables with the same count and type of fields, but the place ot two fields is different
in the first table comapre with the second.

table1(field1(char(1)),field2(char(1)) )
table2(field2(char(1)),field1(char(1)) )

then i want to insert table1.records in table2.recoreds in MSSQLSERVER2000 by using
'INSERT INTO ' statement,but the MSSQLSERVER2000 can not found the difference
and the recoreds are transfered wrongly in the second table.

what can i do????
9/5/2009 2:25 AM | behzad shaterzadeh
Gravatar

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

You could use a tool like diff-r. http://www.izamoo.com/izamoo/index.php?option=com_content&view=article&id=5&Itemid=8
9/13/2009 9:21 PM | Ilyas
Gravatar

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

Please tell me the query:

i have to compare two tables of two different databases, i have read the above posts , when i tried to implement the jeffs method it is not working. can any one tell how to compare the data of two tables which belongs to two different data bases.

for exmaple , table A from tempdatabase and table B from database, both tables have same structure, i want to know the different among these two tables data, want the data that is different in two tables.
9/15/2009 3:18 AM | waqas ahmad
Gravatar

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

Please tell me the query:

i have to compare two tables of two different databases, i have read the above posts , when i tried to implement the jeffs method it is not working. can any one tell how to compare the data of two tables which belongs to two different data bases.

for exmaple , table A from tempdatabase and table B from database, both tables have same structure, i want to know the different among these two tables data, want the data that is different in two tables.
9/15/2009 3:18 AM | waqas ahmad
Gravatar

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

Yes. My requirement is same as mentioned above. I have to find out the diffrences between 2 same tables on same database present on diffrent servers

In short I have to compare

Server1.mydb.table1 with Server2.mydb.table1

You can call it as comparing database table data loaded on diffrent database server
10/13/2009 3:18 PM | Dnyanesh
Gravatar

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

Thanks a lot, very nice and elegant solution!!!
11/2/2009 8:01 AM | Guy A.
Gravatar

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

Would someone please help me with adding the tablename to this code. Everytime I try to add the tablename is list every record, please help!


select sku_cd,prf_alt_sku,eff_dt,exp_dt,gnrc_ds,brand_ds,NDC_cd
from
(
select A.[sku_cd],a.[prf_alt_sku],a.[eff_dt],a.[exp_dt],a.[gnrc_ds],a.[brand_ds],a.[NDC_cd]
from [tbl_SCcatalog] a
union all
select B.[sku_cd],b.[prf_alt_sku],b.[eff_dt],b.[exp_dt],b.[gnrc_ds],b.[brand_ds],b.[NDC_cd]
from [tbl_SCcatalog_compare] b
) tmp
group by [sku_cd],[prf_alt_sku],[eff_dt],[exp_dt],[gnrc_ds],[brand_ds],[NDC_cd]
having count (*) =1
order by [sku_cd];
11/17/2009 3:16 PM | Juanetta
Gravatar

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

Would someone please help me with adding an alias tablename suchas OLD & NEW to this code. Everytime I try to add the alias tablename it list every record, please help! I just need to figure out which rows are from which table. thanks


select sku_cd,prf_alt_sku,eff_dt,exp_dt,gnrc_ds,brand_ds,NDC_cd
from
(
select A.[sku_cd],a.[prf_alt_sku],a.[eff_dt],a.[exp_dt],a.[gnrc_ds],a.[brand_ds],a.[NDC_cd]
from [tbl_SCcatalog] a
union all
select B.[sku_cd],b.[prf_alt_sku],b.[eff_dt],b.[exp_dt],b.[gnrc_ds],b.[brand_ds],b.[NDC_cd]
from [tbl_SCcatalog_compare] b
) tmp
group by [sku_cd],[prf_alt_sku],[eff_dt],[exp_dt],[gnrc_ds],[brand_ds],[NDC_cd]
having count (*) =1
order by [sku_cd];
11/17/2009 5:24 PM | Juanetta
Gravatar

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

Thanks for posting useful tricks.
I found this simplification:
There is no need for A. and B. prefix.
This made my life easier because I had a table with 50 columns and I could just copy the list of columns that QueryAnalyser generates and not have to do anything with it.

SELECT MIN(TableName) as TableName, ID, COL1, COL2, COL3 ...
FROM
(
SELECT 'Table A' as TableName, ID, COL1, COL2, COL3, ...
FROM A
UNION ALL
SELECT 'Table B' as TableName, ID, COL1, COl2, COL3, ...
FROM B
) tmp
GROUP BY ID, COL1, COL2, COL3 ...
HAVING COUNT(*) = 1
11/30/2009 11:14 AM | ILIA BROUDNO
Gravatar

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

Thank you. Thank you!
1/4/2010 8:34 PM | Morris
Gravatar

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

Awesome solution!

If you have the create procedure solution written in PL/SQL for Oracle, please post!

I especially would like Guido's version of the procedure in PL/SQL.

THANK YOU!

1/5/2010 5:59 PM | Lily
Gravatar

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

Quality Solution does what the title says!
1/29/2010 5:50 AM | Rich
Gravatar

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

THANK YOU!!

I've spent the better part of the day trying to (re)create a sync script, and kept running into exceptions. I then came upon this nugget and my workload has been reduced! Thanks!
2/6/2010 11:45 PM | Adam
Gravatar

# Be careful with FLOAT values...

Jeff, great article, very helpful, I've used this code many times!

Just one warning....

FLOAT columns are imprecise and therefore cause this query to label records as mismatches when you're not expecting it.

Thanks,

Matt
2/18/2010 5:55 PM | Matt
Gravatar

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

Nice! I love it's it's clever. Thank you
3/5/2010 8:19 PM | LK
Gravatar

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

I keep getting:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'MyTableName'.

So I am comparing two tables in different DBs, do I just put in the conventional table name without any extensions? E.G.

instead of 'DB1.dbo.MyTableName' just put 'MyTableName' in the MIN() bit?

'SELECT MIN(MyTableName) as MyTableName, ID, [Trigger], Filter1, Filter2, Filter3, Filter4, Filter5'


3/29/2010 8:59 AM | Andrew
Gravatar

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

Hi,

This function is great, but I was looking for something oneway.

I have to tables (A and B) and want only see lines that are in A which are different or missing from B


Is this possible and if so how?

thnx
4/7/2010 10:51 AM | Joop
Gravatar

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

Hi All

Can anybody tell me how can i compare data from two rows from a single file.
Actually what i want is to updated a count flag in the file if an account is appearing two times then in count flag it should come 1 on first occurence and 2 on second,the file is oder by account no.

Thanks in advance.
4/16/2010 7:45 AM | ankur
Gravatar

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

hey;

thank you for your effort it really helped me

but i have a question

i compare two tables A and B , A is the old one, b is the new. now this solution helped me alot but how can i find out which data from A and which one is from B.

For ex , a new row inserted to table B. when i compare data i get this row but how can i know that this row is came from A or B.

thanks alot

chorei
4/21/2010 3:12 AM | chorei
Gravatar

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

Thanks for posting your work on this, it really helped me alot!
5/4/2010 5:10 PM | Rich
Gravatar

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

Very elegant. Nice solution.

But what I was really looking for was someone to wash my car.
5/4/2010 6:01 PM | Ron Carlton
Gravatar

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

Awesome, i never think about this way. Its easy and right to the point solution.
5/13/2010 4:01 AM | Chandra Wijaya
Gravatar

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

if exists (select 1 from sysobjects where id = object_id('dbo.p_get_non_parring_data') and type='P')
drop procedure dbo.p_get_non_parring_data;
go
-------------------------------------------------------------------------------------------------
-- autor: l.skrisovsky --
-- description: procedure with return diffrend records from two indentically tables --
-- parameters: @table1, @table2 -> name of compare table; --
-- @t_rank -> source table (1/2); --
-- @result_type -> all differend records from source table / count record (count) --
-- example use: exec p_get_non_parring_data 't_sale','db_test.dbo.t_sale',1,'count' --
-- exec p_get_non_parring_data 't_sale','db_test.dbo.t_sale',2 --
-------------------------------------------------------------------------------------------------
create procedure p_get_non_parring_data
@table1 varchar(255), @table2 varchar(255), @t_rank int, @result_type varchar(10)='select'
as
begin

declare @tbl_tmp varchar(255);
if(@t_rank=2)
begin
set @tbl_tmp=@table1;
set @table1=@table2;
set @table2=@tbl_tmp;
set @tbl_tmp=@table2;
end;

declare @tab_info table
(
[Col_ID] int primary key
, [Tab_Name] varchar(255)
, [Col_Name] varchar(100)
, [Col_Type] varchar(50)
, [Col_Length] int
);

insert into @tab_info (Col_ID,Tab_Name,[Col_Name],Col_Type, [Col_Length])
select ac.column_id as [Col_ID]
, so.name as [Tab_Name]
, ac.name as [Col_Name]
, st.name as [Col_Type]
, ac.max_length as [Col_Length]
from sys.objects so
join sys.all_columns ac
on so.[object_id]=ac.[object_id]
join sys.types st
on ac.system_type_id=st.system_type_id
where so.name=(case when @t_rank=2 then @tbl_tmp else @table1 end) and [type]='U'
order by ac.column_id;

declare cr_cols cursor for
select [Col_ID], [Col_Name], [Col_Type], [Col_Length] from @tab_info;

declare @c_id int, @c_name varchar(100), @c_type varchar(50), @c_length int;
declare @query_select varchar(max);
set @query_select=case when @result_type='count' then
'select ''Count records with diffrends in table ['+@table1+'] VS ['+@table2+']:'' as [type], count(*) as [count_row] ' else 'select t1.* ' end;
declare @query_join varchar(max);

open cr_cols
fetch next from cr_cols into @c_id, @c_name, @c_type, @c_length;

while @@fetch_status=0
begin
if(@c_id=1) set @query_join='from '+@table1+' t1 left join '+@table2+' t2 ';
set @query_join+=(case when @c_id=1 then 'on' else ' and' end)+
' cast(t1.'+@c_name+' as '+@c_type+')=cast(t2.'+@c_name+' as '+@c_type+')'
fetch next from cr_cols into @c_id, @c_name, @c_type, @c_length;
end;
set @query_join+=' where t2.'+@c_name+' is null';

exec (@query_select+@query_join);

close cr_cols;
deallocate cr_cols;

end;
5/29/2010 10:47 AM | Skrisovsky
Gravatar

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

the union all technique cannot handle scd1/scd2 changes in DW as it picks up any changes
6/2/2010 5:48 PM | Stanislav
Gravatar

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

how would you modify it to only return the columns where the columns are different
6/16/2010 5:05 PM | will
Gravatar

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

Hey if i want to compare data one table (one database) and anther table(anther database), what i have to do ?
6/29/2010 1:23 AM | SANK
Gravatar

# circular reference error

Great code!

The circular reference error is because the original code is missing the word 'AS' on the ninth line. it should be
) AS tmp
7/15/2010 4:26 PM | xstef
Gravatar

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

In Access, 'tmp' needs to be added

SELECT Min(tmp.TableName) AS TableName, tmp.ID, tmp.A, tmp.B, tmp.C, tmp.D, tmp.E
FROM (
SELECT 'Table A' as TableName, sc_head.ID, sc_head.A, sc_head.B, sc_head.C, sc_head.D, sc_head.E
FROM sc_head
UNION ALL
SELECT 'Table B' as TableName, compare_sc_head.ID, compare_sc_head.A, compare_sc_head.B, compare_sc_head.C, compare_sc_head.D, compare_sc_head.E
FROM compare_sc_head
) AS tmp
GROUP BY tmp.ID, tmp.A, tmp.B, tmp.C, tmp.D, tmp.E
HAVING Count(*)=1
ORDER BY tmp.ID;
7/15/2010 4:32 PM | xstef
Gravatar

# Data Type to be used???

Dear All

I'm a new bee to SQL and I have some millions of data to be checked for duplicates, I was using excel and that was taking much longer time and was not that accurate.

I started using the SQL Server and created a table with the following columns:

Column 'A' ( will be of this format - M123456789_1234567890120_1234567890123)
Column 'B' ( will be of this format - M123456789) (Alphanumeric)
Column 'C' ( will be of this format - 1234567890120) (Numeric)
Column 'D' ( will be of this format 1234567890123) (Alpha Nueric)

I'm a not sure about what kind of data type should be used for Column 'A', 'B' and 'D', presently they are assigned float as data type and as far as I have read this is occupying much of my space.

It would be great help if any of you could help me to assign the data type please.

Secondly I want to create different tables for each month with the same fields as every month I will have a million files.

How can I search for duplicate records or rows in two tables which are in same database and for how many tables can I check for duplicates as I mentioned that I need to create new table for every month and I need to verify the new data with the old one. Please if anyone can provide me with the SQL Query syntax for the same.

Your kind help will be appreciated.


8/3/2010 3:21 AM | Mohammed
Gravatar

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

We also need to add a column to each part of the UNION to indicate which table each row comes from, otherwise there is no way to distinguish between which row comes from which table.
10/6/2010 9:35 AM | snow boots for women
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET