views:

197

answers:

6

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

+2  A: 

You can use the TableDiff.exe utility that comes with SQL Server 2005 and above.

You can read more here.

Raj More
THis is quite an impressive tool. I was however hoping for something that can be done within T-SQL code.
Mr Grieves
+2  A: 
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.

Lukasz Lysik
Yeah, I was afraid you'd say that.
Mr Grieves
Don't forget to test for NULL
MrGumbe
Not good? More requirements? Just say, anything can be done here at SO ;-)
Lukasz Lysik
The 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.
Mr Grieves
You can generate this big WHERE statement using Dynamic SQL. Give me few minutes, maybe I'll produce something.
Lukasz Lysik
I've added sp to generate statement for you.
Lukasz Lysik
It is almost correct, but not completely so. NULL and '' are different values, and your ISNULL(t1.field1,'') does not distinguish between them
AlexKuznetsov
This is great, thanks
Mr Grieves
A: 

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
        ....
KM
+2  A: 

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.

Jonas Lincoln
A: 

Just so you are aware, there is a terrific third party tool that you can buy to do this sort of thing for SQL Server databases called SQL Data Compare. It is sold by red-gate. http://www.red-gate.com/products/SQL%5FData%5FCompare/index.htm?gclid=CKPp-6-Bi50CFQRM5QodtWGl8g

There's a free trial. But it's worth spending the money for.

HLGEM
A: 

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.

Philip Kelley