Comparing two T-SQL tables for diffs
Solution 1
SELECT t1.id
FROM table1 t1
INNER JOIN table2 t2 ON t1.id = t2.id
WHERE ISNULL(t1.field1,'') <> ISNULL(t2.field1,'')
OR ISNULL(t1.field2,'') <> ISNULL(t2.field2,'')
OR ...
To produce long WHERE part you can use this function:
CREATE PROCEDURE compareTables
@db1 NVARCHAR(100),
@table1 NVARCHAR(100),
@db2 NVARCHAR(100),
@table2 NVARCHAR(100)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @where NVARCHAR(MAX)
DECLARE @cmd NVARCHAR(MAX)
SET @where = ''
SELECT @where = @where + 'ISNULL(t1.' + name + ','''') <> ISNULL(t2.' + name + ','''') OR '
FROM sys.columns WHERE object_id = OBJECT_ID(@table1)
SET @where = SUBSTRING(@where,1,LEN(@where)-3)
SET @cmd = 'SELECT t1.id FROM ' + @db1 + '.' + @table1 + ' t1 '
SET @cmd = @cmd + 'INNER JOIN ' + @db2 + '.' + @table2 + ' t2 ON t1.id = t2.id '
SET @cmd = @cmd + 'WHERE ' + @where
EXEC sp_executesql @cmd
END
GO
Example usage:
EXEC compareTables 'db1_name','dbo.table1','db2_name','dbo.table1'
Remember to put schema in the table name.
Solution 2
From your text, I think you say there is an ID that can be used for matching rows.
SELECT t1.*, t2.*
FROM table1 t1 JOIN table2 t2 ON t1.id=t2.id
WHERE BINARY_CHECKSUM(t1.*) <> BINARY_CHECKSUM(t2.*)
Untested, but should work.
Solution 3
A bit late, but:
SELECT *
from Table1
except select *
from Table2
will list all rows in Table1 that are not present in Table2,
SELECT *
from Table2
except select *
from Table1
will show all in Table2 that are not in Table1, and
SELECT *
from Table1
intersect select *
from Table2
will show all rows that are identical in both tables. If any colums are known to vary between tables, specify only those columns you need to compare.
Solution 4
You can use the TableDiff.exe utility that comes with SQL Server 2005 and above.
You can read more here.
Solution 5
for SQL Server 2005 and up try something like this:
declare @CurrentTable table (pk int, valuedata varchar(5))
declare @oldtable table (pk int, valuedata varchar(5))
insert into @CurrentTable values (1,'aa')
insert into @CurrentTable values(2,'bb')
insert into @CurrentTable values(3,'cc')
insert into @CurrentTable values(4,'dd')
insert into @CurrentTable values(5,'ee')
insert into @oldtable values(1,'aa')
insert into @oldtable values(1,'bb')
insert into @oldtable values(3,'zz')
insert into @oldtable values(7,'aa')
insert into @oldtable values(8,'qq')
select pk,valuedata from @CurrentTable
except
select pk,valuedata from @oldtable
OUTPUT
pk valuedata
----------- ---------
2 bb
3 cc
4 dd
5 ee
(4 row(s) affected)
if SQL Server 2000 or older try something like this:
SELECT
c.*
FROM YourTableCurrent c
LEFT OUTER JOIN YourTableOld o ON c.id=o.id
WHERE ISNULL(c.IntCol,-2147483648)!=ISNULL(o.IntCol,-2147483648)
OR ISNULL(c.varcharCol,'||null||')!=ISNULL(o.varcharCol,'||null||')
OR ISNULL(c.DatetimeCol,'01/01/1753')!=ISNULL(o.DatetimeCol,'01/01/1753')
....
UNION
SELECT
o.*
FROM YourTableOld o
LEFT OUTER JOIN YourTableCurrent c ON c.id=o.id
WHERE c.id IS NULL
....
srmark
Updated on June 13, 2022Comments
-
srmark about 2 years
I have two instances of the same database. The first db represents data from today, the second data from 6 months ago. I need to find differences for a subset of entries in a specific table.
For entries with ids that are in both tables, I'd like to find a way to view only the rows that aren't identical.
Any ideas?
Thanks
-
srmark almost 15 yearsYeah, I was afraid you'd say that.
-
srmark almost 15 yearsTHis is quite an impressive tool. I was however hoping for something that can be done within T-SQL code.
-
cortijon almost 15 yearsDon't forget to test for NULL
-
Lukasz Lysik almost 15 yearsNot good? More requirements? Just say, anything can be done here at SO ;-)
-
srmark almost 15 yearsThe tables are quite big so I assume that this won't perform very well. I was hoping for some kind of advanced function that could hash rows or something. This is however a usable solution and I thank you for it.
-
Lukasz Lysik almost 15 yearsYou can generate this big WHERE statement using Dynamic SQL. Give me few minutes, maybe I'll produce something.
-
Lukasz Lysik almost 15 yearsI've added sp to generate statement for you.
-
A-K almost 15 yearsIt is almost correct, but not completely so. NULL and '' are different values, and your ISNULL(t1.field1,'') does not distinguish between them
-
Andreas Jansson over 10 yearsIt's only fair to add that there's a number of data compare tools out there... (Red Gate's was not my choice). Here is a list updated a few years ago: mssqltips.com/sqlservertip/1069/sql-server-comparison-tools
-
Andreas Jansson over 10 yearsBINARY_CHECKSUM doesn't allow the asterisk "" symbol for all fields. I get "Incorrect syntax near ''" [asterisk] in 2008R2. It would have been a very nice comparison, if it had worked.
-
Andreas Jansson over 10 yearsHere is a suggestion to take not_nullable and numeric values into account (tables in the same datbase here, add db1 and db2 if needed): SELECT @where = (at)where + CASE WHEN is_nullable = 0 THEN 't1.' + name + ' <> t2.' + name + ' OR ' WHEN precision = 0 THEN 'ISNULL(t1.' + name + ','''') <> ISNULL(t2.' + name + ','''') OR ' ELSE 'ISNULL(t1.' + name + ',-999999) <> ISNULL(t2.' + name + ',-999999) OR ' END FROM sys.columns WHERE object_id = OBJECT_ID((at)table1) (I was not allowed to use more than one @ variable, since SO interpreted them as user names. Sorry for the (at):s.)
-
Jonas Lincoln over 10 yearstechnet.microsoft.com/en-us/library/ms173784(v=sql.105).aspx says it's supported in 2008R2. Is there an other problem with the query perhaps?
-
Andreas Jansson over 10 yearsYou are right: the asterisk is allowed, but the table names are obviously not not allowed. I guess the BINARY_CHECKSUM with (asterisk) generates a checksum for all of the fields, i.e. for the joined tables combined. See stackoverflow.com/questions/9038338/…