Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

In SQL Server 2005, I would like to make one query that checks if all record columns of 2 tables are the same, Example:

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '22' 
--should return false

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' 
insert into @tbl2
    select '11' union select '22'
--should return false

declare @tbl1 table(col nvarchar(50))
declare @tbl2 table(col nvarchar(50))

insert into @tbl1
    select '11' union select '22'
insert into @tbl2
    select '11' union select '22'
--should return true

Using IN clause, or LEFT JOIN i'm only able to check if all record columns in one of the tables are present on the other.

Edit: Order is irrelevant

share|improve this question
1  
Strictly for completeness, you could also use the SQL Server command-line utility, tablediff for this purpose. dba.stackexchange.com/questions/22385/… –  Max Vernon May 13 at 17:47

2 Answers 2

up vote 4 down vote accepted

I think this will work:

SELECT CASE WHEN NOT EXISTS
         ( SELECT * FROM table1
           EXCEPT 
           SELECT * FROM table2
         ) 
             AND NOT EXISTS 
         ( SELECT * FROM table2
           EXCEPT 
           SELECT * FROM table1
         ) 
         THEN 'True' ELSE 'False'
       END AS result ;
share|improve this answer
    
Probably better than my solution as it will give you the differences. –  James Anderson May 13 at 16:42
    
Nope, it does not work for the second case –  RMiranda May 13 at 16:47
    
@RMiranda True, corrected now. –  ypercube May 13 at 16:49
    
Perfect :) Thank you! –  RMiranda May 13 at 16:52

The code below will convert all the values of a column to a string which it then hashes. Running this code on both tables and comparing the two hashes will tell you if they are identical or if there is a difference.

SELECT HASHBYTES('MD5', CONVERT(VARCHAR(20), col)) 
FROM @tbl1  FOR XML PATH('')
share|improve this answer
    
humm, doesn't work if is not in the same order. Forgot to mention. –  RMiranda May 13 at 16:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.