I want to filter records on 'Email' my query is like this.
SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer)
It gives me following error
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.
I can use collate if there is equal operator (=) instead of IN. But using Collate here gives me syntax error.
SELECT * FROM #temp WHERE email NOT IN (SELECT email FROM Customer) collate SQL_Latin1_General_CP1_CI_AS
How can I resolve this. I can't drop and re create tables because it is live db.
Data in #temp table is from SQL Server 2000 and Customer table is in SQL Server 2005
Thanks.