0

Quick question. I'm trying to transfer entire columns from one database table to another. I know this is as simple as

INSERT INTO software(Name, Type)
SELECT (Name, Type)
FROM serials (ProductName, Type)

The Product Name column has a mix of both English and Asian characters. So far Insert queries have been replacing the Asian characters with Squares. I know you can specify the Variable type using N@VariableToInsert. Is there any such function for doing the same with entire columns?

My column type is Nvarchar.

Thanks in Advance

Tinydan
  • 915
  • 4
  • 14
  • 30

1 Answers1

2

Try to cast the column to nvarchar:

INSERT INTO software(Name, Type)
SELECT (CAST(Name as nvarchar), Type)
FROM serials (ProductName, Type)
OriolBG
  • 2,031
  • 2
  • 18
  • 21
  • Maybe is the new database collation. Are both databases with the same collation setting? – OriolBG Jul 18 '13 at 10:54
  • Yeah they are. Had to change my database to match up. Does changing it always work or would you suggest I recreate the database? – Tinydan Jul 18 '13 at 10:56
  • It could give you some problems with the characters. Sometimes you can see only the squares characters but internally they are correct. If both columns have the same type and databases have the same collation settings I don't know what can be. Try to save the results as csv and open it with Notepad++. Maybe the contents are alright but the tool doesn't shows the characters (or you are missing a language package in your computer). – OriolBG Jul 18 '13 at 10:59
  • Hmm perhaps I should try recreating my database to see if it makes a difference. – Tinydan Jul 18 '13 at 11:00
  • @tinydan - changing database collation does not change automatically collation of existing tables and columns. You need to change column collation as well. http://stackoverflow.com/questions/17616620/cannot-resolve-the-collation-conflict-between/17617060#17617060 – Nenad Zivkovic Jul 18 '13 at 11:10
  • Nah created a new DB with fresh collation hasn't changed a bit – Tinydan Jul 18 '13 at 11:11
  • @NenadZivkovic I figured as much, so I created a new Database with the collation I needed. I'm still getting the same result so perhaps One of the columns I'm using has its own collation? – Tinydan Jul 18 '13 at 11:16
  • The collation on both source and destination columns are both set to default database collation. That collation being Latin1_General_CI_AI – Tinydan Jul 18 '13 at 11:21
  • Oddly in changing my database and table collation, the table I'm querying from is now showing squares in place of Asian characters. Does collation always give this much grief? I'm going to mark @OriolBG's answer as correct as I imagine in normal circumstances it'd work – Tinydan Jul 18 '13 at 11:38