How to insert Unicode data into table when pulling data from another table

[cathycros]

Hi,
I'm trying to take data from varchar fields in one table and copy it to Nvarchar fields in another table. (Long story - now dealing with multiple languages, not enough space in row in current table for multiple Nvarchar fields...).

I know how to insert new text into the Nvarchar fields:

INSERT INTO tblNotes ( noteID, note )
SELECT 1, N'This is a unicode note'

but the problem I'm having is when I'm pulling the text to insert from another table:

INSERT INTO tblNotes ( noteID, note )
SELECT tblEvents.eventID, tblEvents.eventTitle
FROM tblEvents;

How do I indicate that the "eventTitle " field should be inserted as Unicode? I can't add a preceding 'N' to it as I would if directly inserting data.

Thanks for any insight.
Cathy

[Jerry Winston]

Try using COLLATE in your SELECT statement.

SELECT myField1COLLATE [SQL_Latin1_General_CP1_CI_AS] FROM someTable

As it turns out SQL Server 2005 uses UCS-2 encoding scheme to simulate Unicode support. So there's really no Unicode UTF-8 collation in SQL 2005. Yes, I know the universe is encoded in UTF but MS picked something obscure for its encoding...