CODEWITHSUNDEEP
sqlsql-servertypestype-conversion
daveb
daveb

Reputation: 3535

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.

Upvotes: 5

Views: 67954

Answers (5)

DavidCC
DavidCC

Reputation: 370

varchar and nvarchar only use the length needed for the data stored. If you need unicode support certainly convert to nvarchar, but modifying it from 50 to 9 - what is the point?

If your data is ALWAYS exactly 9, consider using char(9), and following one of the transformation suggestions above...

Upvotes: 0

Niek
Niek

Reputation: 1019

If you encounter this error during Import/Export Tasks, you can use the select cast(xxx as nvarchar(yyy)) as someName in the "Write a query to specify the data to transfer" option

Upvotes: 0

HoGo
HoGo

Reputation: 158

And it is also important to check field types of destination table. Just spent 3 hours because of same error with random casting, trimming, substring and at the end noticed, that colleague created table with too short field lengths. I hope it helps somebody...

Upvotes: 0

M.Ali
M.Ali

Reputation: 69584

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

Upvotes: 6

Habeeb
Habeeb

Reputation: 1040

Bulk insert into temp table with varchar(50) and insert to actual table

insert into tableName 
select cast(tempcolumn as nvarchar(9)) from temptable

Upvotes: 1

Related Questions