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
Feedback
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Well JeffI 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Hey,This really works!
Thanks
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
how to calculate the percentage?# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Thanks for your work on this!# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
This is what I was founding!!!Good job!
Congratulations!
# 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.
# 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 ---------
# 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?
# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Thanks!!! This really worked out for me.....# 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
# 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!!
# 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]
# 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
# 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
# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
You would be surprised, the answer is yes!# 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
# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Sorry for misspellings!# 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.# 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
# 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 belowSELECT 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
# 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.# 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.# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
This was a butt saver tonight .. thanks# 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?
# 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?
# 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?# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Excellent Work# 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.
# 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?# 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...............
# 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.
# 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
# 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!# 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?# 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.
# 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.
# 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.
# 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.# T-SQL: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
# 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.
# 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..
# 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
# 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
# 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
# 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
# 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
# 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
# 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
# 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
# T-SQL: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION
# 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.
# 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.
# 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.
# 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
# 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.)
# 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
# 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.
# 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!
# 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)
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
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.
# 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..
# 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.
# 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
# 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;
# 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%';
# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
IT REALLY HELPED ME A LOT. THANKS A LOT# 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.# 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# 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
# 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.......:-)
# 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.
# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Thank's a lotThis really help me to compare two tables
Nice Works
# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Cheers....... it was really useful..... Thanks...Sridhar
# 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
# 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,'')
# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Nevermind. Its working.Thanks alot Jeff
# 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
# 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
# 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.
# 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
# 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
# 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!
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Thanks for the code...it helped me a lot...
# 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?
# 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!
# 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.
# 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
# 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
# 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
# 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....
# 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?# 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# 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
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Zoid -- sure!# 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.
# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
Thanx# re: The shortest, fastest, and easiest way to compare two tables in SQL Server: UNION !
u have made my job easier ,thank you# 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
# 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
# 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 tabelsI 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
# 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