SELECT col1, col2 FROM Table1
EXCEPT
SELECT colA,colB from Table2;

-- and the other way around
SELECT colA,colB from Table2
EXCEPT
SELECT col1, col2 FROM Table1;

quote:

---

Originally posted by tkizer

Please show us sample data and expected output to make your question clear.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Hi TKizer, Please find below the example:

Table1 'Person' on Database 'A' hosted on Server 'X'. Below is the table content:
Emp ID,First Name,Last Name,Manager Name,Department,Job Code,Title
100,Raja,Rajan,Mark Thomas,Security,CB001,Senior Analyst
101,Rani,Maria,Steve Cook,Reception,CB005,Receptionist
102,John,Titto, Mark Gill,Administration,CB500,Junior

Table2 'ADSAccount' on Database 'B' hosted on Server 'Y'. Below is the table content:
Resource ID,Given Name,Sur Name,Supervisor,Title
100,Raja,Rajan,Mark Thomas,Analyst
101,Rani,Maria,Steve Cook,NULL
102,John,Titto,Mark Gill,Junior

Expected Output
Emp ID,First Name,Last Name,Manager Name,Department,Job Code,Title
100,NULL,NULL,NULL,NULL,NULL,Senior Analyst
101,NULL,NULL,NULL,NULL,NULL,Receptionist

---

quote:

---

Originally posted by James K

To use a T-SQL query to do this, you will either need to import one of the tables from one server to the other, so both tables are in the same server. Alternatively, you can make a linked server connection from one server to the other and use linked server queries.

Regardless of which way you choose, one way to find the differences would be to use the EXCEPT construct. For example:

SELECT col1, col2 FROM Table1
EXCEPT
SELECT colA,colB from Table2;

-- and the other way around
SELECT colA,colB from Table2
EXCEPT
SELECT col1, col2 FROM Table1;

Hi James,
Thanks for your response. I will try working this out. But, we will not be able to load the data into a table in the same server, hence I might need to use linked server queries to get this done.

Also, got a question. Will using an EXCEPT clause list down the differences in data as well for the destination table by comparing it against the source table?

---