3

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

5

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 ;
| improve this answer | |
1

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('')
| improve this answer | |
  • humm, doesn't work if is not in the same order. Forgot to mention. – RMiranda May 13 '14 at 16:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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