0

In my SQL, I would need to compare data between two tables in SQLServer 2008R2 to return the rows where mismatch is present (using EXCEPT) and likewise matching rows in other cases (INTERSECT). The problem is, some of the columns have NTEXT datatype (SQLServer), and SQLServer gives error when such tables having columns with NTEXT are present.

Example: 

SELECT * FROM table_pre
EXCEPT
SELECT * FROM table_post

The above operation gives an error - 

'The ntext data type cannot be selected as DISTINCT because it is not comparable.'

I believe that tables (table_pre, table_post) have at least one column of datatype = NTEXT that is causing the comparison to fail.

Question - 1. Is there some way to exclude these NTEXT columns from the above comparison, without me having to explicitly list out the column names and excluding the problem column? There's a large number of columns involved and explicitly listing is not easy. 2. Can I just explicitly cast/convert the NTEXT column alone to say VARCHAR, and still go by not having to list down the rest of the columns? 3. Or, in general, can I somehow exclude certain columns by listing those out during such comparisons?

Any suggestions, really appreciated! Thanks.

kallakafar
  • 725
  • 3
  • 11
  • 27
  • Of course it's easy, you just need to click and drag the "columns" from the table in SMSS. – Lamak Jan 30 '15 at 15:35
  • 1. No 2. No 3. No - Need to list all columns or hack in the solution found at http://stackoverflow.com/questions/729197/sql-exclude-a-column-using-select-except-columna-from-tablea – anothershrubery Jan 30 '15 at 15:37

1 Answers1

1

Question - 1. Is there some way to exclude these NTEXT columns from the above comparison,

Yes, use explicitly the column names.

without me having to explicitly list out the column names and excluding the problem column?

Using * is a bad habit, you well deserve the error for abusing it.

There's a large number of columns involved and explicitly listing is not easy

Is actually trivial, build the statement dinamycally

Can I just explicitly cast/convert the NTEXT column alone to say VARCHAR

No. You have to convert to NVARCHAR, the N is very important. But, yes you can convert.

  1. Or, in general, can I somehow exclude certain columns by listing those out during such comparisons

Fortunately no. SQL does not randomly decide what columns are or are not part of a result, so you get the predictability you desire.

So, in conclussion:

  • never use *
  • build complex statements dynamically. SELECT ... FROM sys.columns is your friend, you can easily build it in a few seconds
  • ditch the deprecated TEXT, NTEXT and IMAGE types
Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569