Using COLLATE with UNION

How to use COLLATE with UNION? I want to union 2 tables (both have the same columns and the same types: varchar,int, int, decimal).

I got the following error:


sg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "Serbian_Latin_100_CI_AS" and "Croatian_CI_AS" in the UNION operation.

My SQL statement:

select * from #IA_BIH
union 
select * from #IA_MNE

Where should I insert collate database_default? I tried different combinations, but it didn't work.

Based on the collation names I assume that you are using Microsoft SQL Server.

COLLATE can be used at the database level or the column level. Since you are trying to UNION two tables, using the column collation on the needed columns will resolve your query.

Here is a sample bit of code to help you:

use testdb
GO
CREATE TABLE dbo.Serbian  (Name VARCHAR(20) COLLATE Serbian_Latin_100_CI_AS);
CREATE TABLE dbo.Croatian (Name VARCHAR(20) COLLATE  Croatian_CI_AS);
GO
INSERT INTO dbo.Serbian VALUES ('serbian');
INSERT INTO dbo.Croatian VALUES ('croation');
GO

-- Collate to a particular named collation
SELECT Name COLLATE Serbian_Latin_100_CI_AS as CollatedNameSerbian from dbo.Serbian 
UNION ALL
SELECT Name COLLATE Serbian_Latin_100_CI_AS from dbo.Croatian 
GO
-- Collate to the database default collation
SELECT Name  COLLATE database_default as CollatedNameDBDefault from dbo.Serbian 
UNION ALL
SELECT Name COLLATE database_default from dbo.Croatian 
GO

DROP TABLE dbo.Serbian;
DROP TABLE dbo.Croatian;
GO

Of course, if you have several columns with conflicting collations you will need to define their collations as well.

StackExchange Database Administrators Q#80239, answer score: 8