SQL – Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation

Either change table collation or update query on comparisons using COLLATE DATABASE_DEFAULT

Before (failing)

INSERT INTO     geo.Codes (TheCode, CityID)
SELECT 		d.TheCode, d.CityID
FROM 		#tmp d
LEFT JOIN 	geo.Codes p 
ON 		d.TheCode = p.TheCode 
WHERE 		p.TheCode IS NULL

After (using COLLATE DATABASE_DEFAULT)

INSERT INTO geo.Codes (TheCode, CityID)
SELECT 		d.TheCode, d.CityID
FROM 		#tmp d
LEFT JOIN 	geo.Codes p 
ON 		d.TheCode COLLATE DATABASE_DEFAULT = p.TheCode COLLATE DATABASE_DEFAULT
WHERE 		p.TheCode IS NULL

Leave a Reply

Your email address will not be published. Required fields are marked *