inserting data from one table to another data would be truncated

Question

I'm inserting data from one table to another, the new tables structure is mostly derived from the old table with a few new columns, when i'm running my query i get the error:

String or binary data would be truncated.

The values on my insert query comes from a select statement which returns 70,000 rows so i don't know how to find out what data is causing the error, is there any way to find out?

There are some other question on SO: http://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated. You can also look here: http://blogs.lessthandot.com/index.php/datamgmt/datadesign/how-to-find-what-column/ — Klinger, Nov 19 '16 at 03:27

TT. · Answer 1 · 2016-11-19T07:39:30.763

2

One or more of the columns in the target table have a type that is not wide enough to contain the data from the source table, and the source table columns have data in them that are wider than what the target columns can contain.

For instance the source table has a column X that is of type NVARCHAR(200) and you are trying to copy that to the target table column Y that is of type NVARCHAR(100). The source table has at least one row in it with a value for X that is wider than 100 characters. Copying the column would lose you data and will result in the same error you are getting.

What you need to do is either:

Change the types of columns in the target table that are not wide enough
Be explicit if data loss is intended, and use CAST explicitely. Eg for the example I gave previously, CAST(X AS VARCHAR(100)).

Example:

DECLARE @s TABLE(x NVARCHAR(20));
INSERT INTO @s(x)VALUES(N'12345678901234567890'); -- data in source column wider than what the target column can contain

DECLARE @t TABLE(y NVARCHAR(10)); -- target column is less wide than source column

INSERT INTO @t(y) SELECT x FROM @s; -- this statement will fail with the same error as you have

INSERT INTO @t(y) SELECT CAST(x AS NVARCHAR(10)) FROM @s; -- this statement succeeds, only use if data loss is intended

edited Nov 19 '16 at 07:39

answered Nov 19 '16 at 06:23

TT.

14,883
6
41
77

This does not answer the OP question. He is not asking why he got this error but how to find the data that is causing this error. – David דודו Markovitz Nov 20 '16 at 04:55
@DuduMarkovitz The point I'm making is that he shouldn't be looking for the data that is causing the error in the first place. He should change his way of working: either have the target table's columns be at least as wide the source's or be explicit in casting with data loss. Because what is he going to do once he runs a script to find the column / data that is the culprit? – TT. Nov 20 '16 at 08:05
@DuduMarkovitz Don't get me wrong though =), you do have a point. But does your answer *"find out what data is causing the error"*? – TT. Nov 20 '16 at 08:09
The point was clear, but the challenge of the OP here is to find the columns that need to be changed. – David דודו Markovitz Nov 20 '16 at 08:11
The plain answer is "No". I considered adding a code that generates a data length comparison query, but it seems like a overkill at this point since I assume my current answer answers the real need. – David דודו Markovitz Nov 20 '16 at 08:14

David דודו Markovitz · Answer 2 · 2016-11-20T05:17:31.670

This is a lightweight check (metadata only) that will narrow your search down to the list of columns that might causing the issue -
the columns where the max_length in the target table is smaller than the the max_length of the matching expression in the source query.

Create an empty table based on the results of your source query and compare metadata.

Example:

create table src (str1 varchar(11),str2 varchar(3));
create table trg (str1 varchar(7),str2 varchar(3));    
insert into src values ('Hi','XY'),('Hello world','XY'),('Hi','XYZ')

insert into trg (str1,str2) select str1,str2 + 'D' from src

Msg 8152, Level 16, State 14, Line 10
String or binary data would betruncated.

select  str1,str2+'D' as str2 into tmp from src where 1=2;

select      *

from       (select      c.name
                       ,min(case o.name when 'tmp' then t.name end)         as tmp_type
                       ,min(case o.name when 'trg' then t.name end)         as trg_type
                       ,min(case o.name when 'tmp' then c.max_length end)   as tmp_max_length
                       ,min(case o.name when 'trg' then c.max_length end)   as trg_max_length

            from                    sys.objects as o
                        join        sys.columns as c
                        on          c.object_id         = o.object_id
                        join        sys.types   as t
                        on          t.system_type_id    = c.system_type_id
                                and t.user_type_id      = c.user_type_id

            where       o.name in ('tmp','trg')
                    and (   c.collation_name is not null 
                        or  c.name in ('binary','varbinary')
                        )

            group by    c.name
            ) c

where       tmp_max_length >  trg_max_length

order by    c.name
;

+------+----------+----------+----------------+----------------+
| name | tmp_type | trg_type | tmp_max_length | trg_max_length |
+------+----------+----------+----------------+----------------+
| str1 | varchar  | varchar  | 11             | 7              |
+------+----------+----------+----------------+----------------+
| str2 | varchar  | varchar  | 4              | 3              |
+------+----------+----------+----------------+----------------+

inserting data from one table to another data would be truncated

2 Answers2