CODEWITHSUNDEEP
sqlsql-server
Nuru Salihu
Nuru Salihu

Reputation: 4928

How to select record of different data type from sql column

I have two a table and a view . The table if of two rows of datatypes nvarchar and money. I have being updating the table by selecting from the view like below.

Insert into MyTable
    Select * from MyView

Recently, this update fails due to an error "String or binary data would be truncated." However, when i modified by select statement to something like.

Select * from Myview WHERE Column is not null 

OR

Select * from Myview WHERE Column > 0

The above work with a warning saying Warning: Null value is eliminated by an aggregate or other SET operation. . It occurred to me that may may be one of the null value records contain something that's not null. My table column is of money type and accept null. I presumed the error may be due to something that's not of money data type. The record is huge. Is there any way i can filter and return those aliens records?

I also i learnt that i can eliminate the error by turning ANSI WARNING SETTION ON & OFF Here . My concern is wouldn't that result in loss of data. Please any help would be appreciated.

Upvotes: 0

Views: 2154

Answers (3)

Md Rashedul Hoque Bhuiyan
Md Rashedul Hoque Bhuiyan

Reputation: 10631

String or binary data would be truncated is a very common error. It usually happens when we try to insert any data in string (varchar,nvarchar,char,nchar) data type column which is more than size of the column. So you need to check the data size with respect to the column width and identify which column is creating problem and fix it.

Here is another thread of the same problem as yours in stackoverflow. string or binary data would be truncated

Hope this will help. Regards

Upvotes: 2

Aladin Hdabe
Aladin Hdabe

Reputation: 865

String or binary data would be truncated happened because the data coming from the MyView is larger than the column size in MyTable

Use

Select Max(Len(FieldName)) From MyTable

to check the maximum length of the nvarchar field in the MyTable Or you can use Left when inserting data something Llike this

Insert into MyTable
Select Left(FieldName,50), Column1 from MyView

Note the 50 should be the size of the nvarchar field in MyTable

Upvotes: 2

yuan
yuan

Reputation: 1153

looks like the data in some column in table MyView exceeds the limit of the corresponding one in table MyTable

Upvotes: 1

Related Questions