CODEWITHSUNDEEP
t-sqlcollation
FinnNk
FinnNk

Reputation: 3265

Can I specify the collation when performing a SELECT INTO

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.

Upvotes: 7

Views: 10997

Answers (3)

Ádám Koczka
Ádám Koczka

Reputation: 23

To correct Kasia's answer:

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

You have to add an alias for each column to get this work.

Upvotes: 1

Kasia
Kasia

Reputation: 11

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

Upvotes: 1

gbn
gbn

Reputation: 432180

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...

Upvotes: 3

Related Questions