EXCEPT SQL statement in SQL Server not returning expected results

Question

I have two tables which are identical but reside on different servers. One table is missing a row which I have verified. I am trying to compare these tables for finding the missing records

by

SELECT  GUID FROM dbo.tableA
EXCEPT 
SELECT  GUID FROM [Server2].MyDatabase.dbo.tableA

or the other way around

SELECT GUID FROM [Server2].MyDatabase.dbo.tableA
EXCEPT 
SELECT GUID FROM dbo.tableA

Both queries return zero results. I have used select count(*) on both tables and the count is different

What am I doing wrong?

Thanks for any feedback in advance

Gordon Linoff · Accepted Answer · 2016-11-25 22:56:21Z

6

The count can be different and it is still possible for except to return zero rows. How? If there are duplicates in one or both tables.

Here is a modification that can help you find the differences:

SELECT GUID, ROW_NUMBER() OVER (PARTITION BY GUID ORDER BY GUID) as seqnum
FROM dbo.tableA
EXCEPT 
SELECT GUID, ROW_NUMBER() OVER (PARTITION BY GUID ORDER BY GUID) as seqnum
FROM [Server2].MyDatabase.dbo.tableA

answered Nov 25, 2016 at 22:56

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mendosi · Accepted Answer · 2016-11-25 23:50:20Z

0

In my experience the EXCEPT operator does not function as expected when working with linked servers. Why? I don't know.

As a workaround I insert the rows from the remote server into a local temp table and then compare: (I also copy the local one to a second temp table for other reasons)

Select GUID
  Into #Server2_tblA
  From [Server2].MyDatabase.dbo.tableA;

Select GUID
  Into #Local_tblA
  From dbo.tableA;

Select GUID From #Server2_tblA
Except
Select GUID From #Local_tblA;
...

answered Nov 25, 2016 at 23:50

mendosi

2,0611 gold badge14 silver badges18 bronze badges

Comments

Mansoor · Accepted Answer · 2016-11-26 18:44:30Z

0

Table A values does not presented in MyDatabase.dbo.tableA.Using Not exist caluse for get this result

SELECT GUID 
FROM dbo.tableA WHERE NOT EXISTS(SELECT 1 
                                 FROM [Server2].MyDatabase.dbo.tableA 
                                 WHERE GUID = [Server2].MyDatabase.dbo.tableA.GUID )

edited Nov 26, 2016 at 18:44

user330315

answered Nov 26, 2016 at 9:16

Mansoor

4,1921 gold badge19 silver badges27 bronze badges

1 Comment

Aurora0001 Over a year ago

While this may be a valid answer, you are much more likely to help others by explaining what the code does and how it works. Code-only answers tend to receive less positive attention and aren't as useful as other answers.

Collectives™ on Stack Overflow

EXCEPT SQL statement in SQL Server not returning expected results

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related