TSQl EXCEPT validation

Question

Hi I am writing a long program in TSQL that pulls in data from an OLD (and very dirty data set) scrubs the data and reformats the output including column headers to match a new data set There are 130 columns in both the new and old tables. For the purpose of testing I am bringing in 100k rows from each. To validate that the table structures and field attributes are the same my boss has asked me to use the TSQL EXCEPT. I got onto our 2008 server and pulled in the data from new table 2005 data and data from the 2008 old archived data.

The code I am using is as follows:

SELECT * FROM #NEW_TABLE
EXCEPT
SELECT * FROM #OLD_TABLE

The EXCEPT statement outputs all 100k records from the new table. Would I be correct that this is because there is no match in the data, rather than table structure issues as I am receiving no errors at all.

Answer 1

Yes - as far as I understand you get all the records on the new table because none of them match the ones on the old table (purpose of EXCEPT)

Answer 2

That's correct, but it depends on what you mean by "match".

In order for EXCEPT to work, all columns must match from both tables in order for it to be removed from the result set. That means that if you have something that looks like:

ID  Name   Salary
--- ------ ------
1   John   550k
2   Sue    500k
3   Bill   40k

and you use EXCEPT with the following:

ID  Name   Salary
--- ------ ------
1   John   520k

You will still see as your output:

ID  Name   Salary
--- ------ ------
1   John   550k
2   Sue    500k
3   Bill   40k