8

I have a query which selects rows from a source database (DatabaseA), and inserts them into a target database (Database B). The collation type differs between the databases and they cannot be changed. I need to address the collation difference in my query by explicitly specifying the collation for varchar fields.

Currently my query looks like this:

INSERT INTO DatabaseB.dbo.Users(
    Id,
    UserNumber,
    FirstName,
    Surname,
    Address1,
    Address2,
    AddressTown,
    AddressCity
)
SELECT 
    Id,
    UserNumber,
    FirstName COLLATE SQL_Latin1_General_CI_AS,
    Surname COLLATE SQL_Latin1_General_CI_AS,
    Address1 COLLATE SQL_Latin1_General_CI_AS,
    Address2 COLLATE SQL_Latin1_General_CI_AS,
    AddressTown COLLATE SQL_Latin1_General_CI_AS,
    AddressCity COLLATE SQL_Latin1_General_CI_AS
FROM DatabaseA.dbo.Users

My question is, can I avoid typing the collation type for every string-based field? Is there a way that I can specify the collation type for the whole query at once? If this is not possible, are there any other shortcuts?

  • There is no shortcut. You can use database_default instead (If you prefer) and if that is correct in this case. – SqlWorldWide May 10 '17 at 11:01
9

You can once create a view using column definitions like this:

FirstName COLLATE SQL_Latin1_General_CI_AS, ...

Then anytime you'd like to use User tables, you can use this view instead.

5

Collations are per each string column / expression (XML datatype excluded). So in this sense, no, Collations cannot be set per Table or query.

I need to address the collation difference in my query by explicitly specifying the collation for varchar fields.

Um, why? You shouldn't need to deal with this for an INSERT statement. Collation conflicts arise either when comparing two columns, or combining two or more columns in a UNION. As you can see with the test below (just run it in a Database that is not tempdb), there are two tables, each in a different database, each having a different Collation -- one being a SQL Server Collation and one being a Windows Collation even -- and there is no error. If you are getting an error, please provide more details in the question.

--DROP TABLE #tmp;
CREATE TABLE #tmp (Col1 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS);
INSERT INTO #tmp ([Col1]) VALUES ('n');

-- DROP TABLE dbo.Tmp;
CREATE TABLE dbo.Tmp (Col1 VARCHAR(50) COLLATE Hebrew_100_CS_AI);

INSERT INTO dbo.Tmp ([Col1])
  SELECT t.[Col1]
  FROM #tmp t;
  • How about this one?<br/> code CREATE TABLE #tmp (Col1 VARCHAR(50) COLLATE Cyrillic_General_CI_AS); INSERT INTO #tmp ([Col1]) VALUES (N'кириллическая строка'); -- DROP TABLE dbo.Tmp; CREATE TABLE dbo.Tmp (Col1 VARCHAR(50) COLLATE SQL_Latin1_General_CP1_CI_AS); INSERT INTO dbo.Tmp ([Col1]) SELECT t.[Col1] FROM #tmp t; select * from dbo.Tmp; code – sepupic May 10 '17 at 19:37
  • I'm sorry, the formatting in comments is different and I cannot enter the code in readable form; I just want to say that the fact you get no error does not mean you did a right thing. Try to exec my awfully formatted code where I copy ascii cyrillic caracters into a column with Latin collation, or modify your example so that you copy Hebrew ascii caracters into non-unicode column with Latin collation, you'll get ?????? instead of your data, meaning you LOST your data forever – sepupic May 10 '17 at 19:47
  • Yes, you are absolutely correct. However, the question says nothing of character conversion being the problem, or NVARCHAR / Unicode. The current wording somewhat implies VARCHAR to VARCHAR. Please specify both Collations in the question, and if this is a one time operation or ongoing. Also, single back quote will format code in comments. Help (can get to from the editor) has a page of markup supported in comments :-) – Solomon Rutzky May 10 '17 at 22:37
  • Thanks for your help with this. Unfortunately the real world issue is not as simple as the example I created above. I made it like that so that the problem was easy to visualise. The only solution is to specify the collation type for each varchar field :( – Laurence Frost May 12 '17 at 15:00
  • @sepupic Sorry about requesting that you update the question. I was replying on my phone and got mixed up on the Login names. I ran your example and it doesn't make sense as of course you would get ? since the Cyrillic characters don't exist in Code Page 1252 (i.e. Latin1_General_CP1, which is used in the destination table). That scenario does not benefit from having the COLLATE keyword added, so no need to address it as the O.P. seems to be asking about a scenario where adding COLLATE does help. – Solomon Rutzky May 12 '17 at 22:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.