BULK INSERT and collation problem

[msx23]

I'm trying to use BULK INSERT to import a text file and then check its contents against a table in the database. I'm getting a problem when I compare the results however. I'm making a temp table to hold the text file's contents

CREATE TABLE #Temp(Code varchar(5), number varchar(2))

Then using BULK INSERT to get the contents in

BULK INSERT #Temp FROM 'PATH.txt' WITH (FIELDTERMINATOR = ',',ROWTERMINATOR = '\n')

And then checking if any matches occur

SELECT Code, (CASE WHEN
(SELECT Count(DBTable.Code) From DBTableWHERE DBTable.Code= #Temp.Code) > 0
THEN "Yes"
ELSE "No" END) AS FLAG
From #TempCCServs

The problem is that I get the following error message now: "Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation." Anyone have an idea what I'm doing wrong? Thanks for any help.

[Syed Mehroz Alam]

The two columns in the comparison differ in collation settings. The following code applies sames collation to both sides before the comparison. SELECT Count(DBTable.Code) From DBTable WHERE DBTable.Code collate Latin1_General_CI_AS = #Temp.Code collate Latin1_General_CI_AS Hope that helps. Regards, Syed Mehroz Alam

My Articles