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

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:

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

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

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

Try using COLLATE in your SELECT statement.

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