Can I specify the collation when performing a SELECT INTO

Question

If I'm selecting from one source into another can I specify the collation at the same time.

e.g.

SELECT Column1, Column2
INTO DestinationTable
FROM SourceTable

Where 'DestinationTable' doesn't already exist.

I know I can do something like

SELECT Column1, Column2 COLLATE Latin1_General_CI_AS
INTO DestinationTable
FROM SourceTable

In my real problem the data types of the column aren't known in advance so I can't just add the collation to each column. It's in a corner of a legacy application using large nasty stored procedures that generate SQL and I'm trying to get it working on a new server that has a different collation in tempdb with minimal changes.

I'm looking for something like:

SELECT Column1, Column2
INTO DestinationTable COLLATE Latin1_General_CI_AS
FROM SourceTable

But that doesn't work.

Answer 1

Can you create the table first?

You can define a collation for the relevant columns. On INSERT, they will be coerced.

It sounds like you don't know the structure of the target table though... so then no, you can't without dynamic SQL. Which will make things worse...

Answer 2

You can do this like that if it helps:

SELECT *
INTO DestinationTable
FROM
(
SELECT Column1  COLLATE Latin1_General_CI_AS, Column2  COLLATE Latin1_General_CI_AS
FROM SourceTable
) as t