daveb daveb - 6 months ago 16
SQL Question

Converting varchar to nvarchar in SQL Server failed

I have SQL Server table that contains columns of type varchar (50) as a result of a CSV import
using the SQL Server Import wizard

I was wanting to know how I can change this data type to nvarchar (9) without getting a SQL Server truncation error

I tried doing a bulk update to set the data types and column sizes that I need but still
had the truncation error message when I tried to load the csv into the empty database table I created (with my required data types that I need)

Grateful for any help

Answer

Since you are willing to lose data and nvarchar will only be able to store 9 non-unicode charaters, then select only 9 characters from your source table, You do the truncation rather than Sql server doing it for you.

The Following Query will trim any White spaces from the strings, Then take only 9 characters from the string and convert them to NVARCHAR(9) for you.....

CREATE TABLE New_TABLE (Col1 NVARCHAR(9), Col2 NVARCHAR(9))
GO

INSERT INTO New_TABLE (Col1, Col2)
SELECT CONVERT(NVARCHAR(9),LEFT(LTRIM(Col1), 9))  
      ,CONVERT(NVARCHAR(9),LEFT(LTRIM(Col2), 9))
FROM Existing_Table
GO