SQL Server String or binary data would be truncated

Question

I am involved in a data migration project  I am getting the following error when I try to insert data from one table into another table  SQL Server 2005       Msg 8152  Level 16  State 13  Line 1   String or binary data would be truncated    The source data columns match the data type and are within the length definitions of the destination table columns so I am at a loss as to what could be causing this error

User · Answer

I ve built a stored procedure that analyses a source table or query with several characteristics per column among which the minimum length  min len  and maximum length  max len    CREATE PROCEDURE  dbo   sp analysetable       tableName varchar 8000      deep bit   0   AS     sp analysetable  company  sp analysetable  select   from company where name is not null      DECLARE  intErrorCode INT   errorMSG VARCHAR 500    tmpQ NVARCHAR 2000    column name VARCHAR 50    isQuery bit SET  intErrorCode 0  IF OBJECT ID  tempdb    tmpTableToAnalyse   IS NOT NULL BEGIN   DROP TABLE   tmpTableToAnalyse END IF OBJECT ID  tempdb    tmpColumns   IS NOT NULL BEGIN   DROP TABLE   tmpColumns END  if CHARINDEX  from    tableName  gt 0   set  isQuery 1  IF  intErrorCode 0 BEGIN   if  isQuery 1 begin     --set  tableName    USE    db     replace  tableName   from    into   tmpTableToAnalyse from       --replace only first occurance  Now multiple froms may exists  but first from will be replaced with into    from     set  tableName Stuff  tableName  CharIndex  from    tableName   Len  from     into   tmpTableToAnalyse from       exec  tableName      IF OBJECT ID  tempdb    tmpTableToAnalyse   IS NULL BEGIN       set  intErrorCode 1       SET  errorMSG  Error generating temporary table from query       end     else begin       set  tableName    tmpTableToAnalyse      end   end end  IF  intErrorCode 0 BEGIN   SET  tmpQ  USE   DB NAME       CHAR 13  CHAR 10      select     c column name as  column       cast sp value as varchar 1000   as description      tc fk constraint type      kcu pk table name as fk table      kcu pk column name as fk column      c ordinal position as pos      c column default as  default       c is nullable as  null       c data type      c character maximum length as length      c numeric precision as  precision       c numeric precision radix as radix      cast null as bit  as  is unique       cast null as int  as min len      cast null as int  as max len      cast null as int  as nulls      cast null as int  as blanks      cast null as int  as numerics      cast null as int  as distincts      cast null as varchar 500   as distinct values      cast null as varchar 50   as remarks   into   tmpColumns    if  isQuery 1 begin     SET  tmpQ  tmpQ   from tempdb information schema columns c   select null as value  sp    end   else begin     SET  tmpQ  tmpQ         from information schema columns c       left join sysobjects so    on so name c table name  and so xtype   U         left join syscolumns sc    on sc name c column name and sc id   so id        left join sys extended properties sp on sp minor id   sc colid AND sp major id   sc id and sp name   MS Description           left join information schema key column usage kcu fk    on kcu fk table name   c table name     and c column name   kcu fk column name       left join information schema table constraints tc fk    on kcu fk table name   tc fk table name and kcu fk constraint name   tc fk constraint name       left join information schema referential constraints rc on rc constraint name   kcu fk constraint name       left join information schema table constraints tc pk    on rc unique constraint name   tc pk constraint name       left join information schema key column usage kcu pk    on tc pk constraint name   kcu pk constraint name      end   SET  tmpQ  tmpQ   where c table name        tableName         exec  tmpQ  end  IF  intErrorCode 0 AND  deep   1 BEGIN   DECLARE      count rows int       count distinct int       count nulls int       count blanks int       count numerics int       min len int       max len int       distinct values varchar 500    DECLARE curTmp CURSOR LOCAL FAST FORWARD FOR     select  column  from   tmpColumns    OPEN curTmp   FETCH NEXT FROM curTmp INTO  column name   WHILE   FETCH STATUS   0 and  intErrorCode 0 BEGIN     set  tmpQ    USE   DB NAME      SELECT             count rows count 0     char 13  char 10             count distinct count distinct     column name       char 13  char 10             count nulls sum case when     column name    is null then 1 else 0 end    char 13  char 10             count blanks sum case when ltrim     column name          then 1 else 0 end    char 13  char 10             count numerics sum isnumeric     column name        char 13  char 10             min len min len     column name        char 13  char 10             max len max len     column name       char 13  char 10           from     tableName         exec sp executesql  tmpQ                         N  count rows int OUTPUT                            count distinct int OUTPUT                            count nulls int OUTPUT                            count blanks int OUTPUT                            count numerics int OUTPUT                            min len int OUTPUT                            max len int OUTPUT                           count rows     OUTPUT                          count distinct OUTPUT                          count nulls    OUTPUT                          count blanks    OUTPUT                          count numerics OUTPUT                          min len        OUTPUT                          max len        OUTPUT      IF   count distinct gt 10  BEGIN       SET  distinct values  Many    cast  count distinct as varchar          END ELSE BEGIN       set  distinct values null       set  tmpQ   N USE   DB NAME                   select  distinct values COALESCE  distinct values       cast     column name    as varchar    cast     column name    as varchar    char 13  char 10              from    char 13  char 10                select distinct     column name    from     tableName    where     column name    is not null  a  char 13  char 10        exec sp executesql  tmpQ                           N  distinct values varchar 500  OUTPUT                             distinct values        OUTPUT     END     UPDATE   tmpColumns SET       is unique       case when  count rows  count distinct then 1 else 0 end        distincts        count distinct        nulls            count nulls        blanks           count blanks        numerics         count numerics        min len          min len        max len          max len        distinct values  distinct values        remarks                 case when  count rows  count nulls then  all null   else    end          case when  count rows  count distinct then  unique   else    end          case when  count distinct 0 then  empty   else    end          case when  min len  max len then  same length   else    end          case when  count rows  count numerics then  all numeric   else    end     WHERE  column   column name      FETCH NEXT FROM curTmp INTO  column name   END   CLOSE curTmp DEALLOCATE curTmp END  IF  intErrorCode 0 BEGIN   select   from   tmpColumns order by pos end  IF  intErrorCode 0 BEGIN --Clean up temporary tables   IF OBJECT ID  tempdb    tmpTableToAnalyse   IS NOT NULL BEGIN     DROP TABLE   tmpTableToAnalyse   END   IF OBJECT ID  tempdb    tmpColumns   IS NOT NULL BEGIN     DROP TABLE   tmpColumns   END end  IF  intErrorCode lt  gt 0 BEGIN   RAISERROR  errorMSG  12  1  END RETURN  intErrorCode   I store this procedure in the master database so that I can use it in every database like so   sp analysetable  table name   1    deep 1 for doing value analyses   And the output is    column              description constraint type fk table        fk column          pos default       null data type        length precision radix is unique min len max len nulls blanks numerics distincts distinct values remarks id individual       NULL        PRIMARY KEY     NULL            NULL               1   NULL          NO   int              NULL   10        10    1         1       2       0     0      70       70        Many  70        unique all numeric  id brand            NULL        NULL            NULL            NULL               2   NULL          NO   int              NULL   10        10    0         1       1       0     0      70       2         2 3             same length all numeric  guid                NULL        NULL            NULL            NULL               3    newid        NO   uniqueidentifier NULL   NULL      NULL  1         36      36      0     0      0        70        Many  70        unique same length  customer id         NULL        NULL            NULL            NULL               4   NULL          YES  varchar          50     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  email               NULL        NULL            NULL            NULL               5   NULL          YES  varchar          100    NULL      NULL  0         4       36      0     0      0        31        Many  31  mobile              NULL        NULL            NULL            NULL               6   NULL          YES  varchar          50     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  initials            NULL        NULL            NULL            NULL               7   NULL          YES  varchar          50     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  title short         NULL        NULL            NULL            NULL               8   NULL          YES  varchar          50     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  title long          NULL        NULL            NULL            NULL               9   NULL          YES  varchar          50     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  firstname           NULL        NULL            NULL            NULL               10  NULL          YES  varchar          50     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  lastname            NULL        NULL            NULL            NULL               11  NULL          YES  varchar          50     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  address             NULL        NULL            NULL            NULL               12  NULL          YES  varchar          100    NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  pc                  NULL        NULL            NULL            NULL               13  NULL          YES  varchar          10     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  kixcode             NULL        NULL            NULL            NULL               14  NULL          YES  varchar          20     NULL      NULL  0         NULL    NULL    70    0      0        0         NULL            all null empty  date created        NULL        NULL            NULL            NULL               15   getdate      NO   datetime         NULL   NULL      NULL  1         19      19      0     0      0        70        Many  70        unique same length  created by          NULL        NULL            NULL            NULL               16   user name    NO   varchar          50     NULL      NULL  0         13      13      0     0      0        1         loyalz-public   same length  id location created NULL        FOREIGN KEY     location        id location        17  NULL          YES  int              NULL   10        10    0         1       1       0     0      70       2         1 2             same length all numeric  id individual type  NULL        FOREIGN KEY     individual type id individual type 18  NULL          YES  int              NULL   10        10    0         NULL    NULL    70    0      0        0         NULL            all null empty  optin               NULL        NULL            NULL            NULL               19  NULL          YES  int              NULL   10        10    0         1       1       39    0      31       2         0 1             same length

User · Answer

You will need to post the table definitions for the source and destination tables for us to figure out where the issue is but the bottom line is that one of your columns in the source table is bigger than your destination columns   It could be that you are changing formats in a way you were not aware of   The database model you are moving from is important in figuring that out as well

User · Answer

I came across this problem today  and in my search for an answer to this minimal informative error message i also found this link   https   connect microsoft com SQLServer feedback details 339410 please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name  So it seems microsoft has no plans to expand on error message anytime soon   So i turned to other means    I copied the errors to excel    1 row s  affected    1 row s  affected    1 row s  affected  Msg 8152  Level 16  State 14  Line 13 String or binary data would be truncated  The statement has been terminated    1 row s  affected   counted the number of rows in excel  got to close to the records counter that caused the problem    adjusted my export code to print out the SQL close to it    then ran the 5 - 10 sql inserts around the problem sql and managed to pinpoint the problem one  see the string that was too long  increase size of that column and then big import file ran no problem   Bit of a hack and a workaround  but when you left with very little choice you do what you can

User · Answer

I had a similar issue   I was copying data from one table to an identical table in everything but name    Eventually I dumped the source table into a temp table using a SELECT INTO statement   SELECT   INTO TEMP TABLE FROM SOURCE TABLE    I compared the schema of the source table to temp table   I found one of the columns was a varchar 4000  when I was expecting a varchar 250      UPDATE  The varchar 4000  issue can be explained here in case you are interested   For Nvarchar Max  I am only getting 4000 characters in TSQL    Hope this helps

User · Answer

As others have already said  one of your columns datatypes in the source table is larger than your destination columns   A simple solution is to simply turn off the warning and allow truncation to take place  So  if you re receiving this error but you are sure it is acceptable for data in your old database table to be truncated  cut to size  you can simply do the following   SET ANSI WARNINGS  OFF  -- Your insert TSQL here  SET ANSI WARNINGS ON    As above  always remember to turn warnings back on again afterwards  I hope this helps

User · Answer

Yep -  a pint into a half-pint pot will not go   I ve not had much luck  for whatever reason  with the various SPs that folks have suggested  BUT as long as the two tables are in the same DB  or you can get them into the same DB   you can use INFORMATION SCHEMA COLUMNS to locate the errant field s   thusly   select c1 table name c1 COLUMN NAME c1 DATA TYPE c1 CHARACTER MAXIMUM LENGTH c2 table name c2 COLUMN NAME  c2 DATA TYPE c2 CHARACTER MAXIMUM LENGTH from  INFORMATION SCHEMA   COLUMNS  c1 left join  INFORMATION SCHEMA   COLUMNS  c2 on  c1 COLUMN NAME c2 COLUMN NAME where c1 TABLE NAME  MyTable1  and c2 TABLE NAME  MyTable2  --and c1 DATA TYPE lt  gt c2 DATA TYPE --and c1 CHARACTER MAXIMUM LENGTH  lt  gt  c2 CHARACTER MAXIMUM LENGTH order by c1 COLUMN NAME   This will let you scroll up and down  comparing field lengths as you go  The commented sections let you see  once uncommented  obviously  if there are data type mismatches  or specifically show those that differ in field length - cos I m too lazy to scroll - just be aware that the whole thing is predicated on the source column names matching those of the target

User · Answer

I am going to add one other possible cause of this error just because no one has mentioned it and it might help some future person  since the OP has found his answer   If the table you are inserting into has triggers  it could be the trigger is generating the error  I have seen this happen when table field definitions were changed  but audit tables were not

User · Answer

One other potential reason for this is if you have a default value setup for a column that exceeds the length of the column  It appears someone fat fingered a column that had a length of 5 but the default value exceeded the length of 5  This drove me nuts as I was trying to understand why it wasn t working on any insert  even if all i was inserting was a single column with an integer of 1  Because the default value on the table schema had that violating default value it messed it all up - which I guess brings us to the lesson learned - avoid having tables with default value s in the schema

User · Answer

this can also happen when you dont have adequate permissions

User · Answer

Here is a slightly different answer   Your column names  amp  lengths may all match  but perhaps you are specifying the columns in the wrong order in your SELECT statement   Say tableX and tableY have columns with the same name  but in different order

User · Answer

Please try the following code   CREATE TABLE  dbo   Department        Department name  char 10  NULL    INSERT INTO  dbo   Department   Department name   VALUES    Family Medicine   --error will occur   ALTER TABLE  Department  ALTER COLUMN  Department name  char 50   INSERT INTO  dbo   Department   Department name   VALUES    Family Medicine    select   from  Department

User · Answer

If you   re on SQL Server 2016-2017  to fix it  turn on trace flag 460  DBCC TRACEON 460  1   GO   and make sure you turn it off after   DBCC TRACEOFF 460  1   GO   source

User · Answer

I wrote a useful store procedure to help identify and resolve the problem of text truncation  String or binary data would be truncated  when the INSERT SELECT statement is used  It compares fields CHAR  VARCHAR  NCHAR AND NVARCHAR only and returns an evaluation field by field in case of being the possible cause of the error    EXEC dbo GetFieldStringTruncate SourceTableName  TargetTableName      This stored procedure is oriented to the problem of text truncation when an INSERT SELECT statement is made       The operation of this stored procedure depends on the user previously identifying the INSERT statement with the problem  Then inserting the source data into a global temporary table  The SELECT INTO statement is recommended       You must use the same name of the field of the destination table in the alias of each field of the SELECT statement    FUNCTION CODE   DECLARE  strSQL nvarchar 1000  IF NOT EXISTS  SELECT   FROM dbo sysobjects where id   OBJECT ID N  dbo   GetFieldStringTruncate         BEGIN         SET  strSQL    CREATE PROCEDURE  dbo   GetFieldStringTruncate  AS RETURN          EXEC sys sp executesql  strSQL     END  GO  SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO     ------------------------------------------------------------------------------------------------------------------------     Description                          Syntax                      ---------------                     dbo GetFieldStringTruncate SourceTable  TargetTable                       --------------------------- -----------------------                          SourceTableName             VARCHAR 255                                --------------------------- -----------------------                          TargetTableName             VARCHAR 255                                --------------------------- -----------------------                       Arguments                     ---------------                     SourceTableName                     The name of the source table  It should be a temporary table using double charp       E g     temp                       TargetTableName                     The name of the target table  It is the table that receives the data used in the INSERT INTO stament                       Return Type                     ----------------                     Returns a table with a list of all the fields with the type defined as text and performs an evaluation indicating which field would present the problem of string truncation                       Remarks                     ----------------                     This stored procedure is oriented to the problem of text truncation when an INSERT SELECT statement is made                      The operation of this stored procedure depends on the user previously identifying the INSERT statement with the problem  Then inserting the source data into a global temporary table  The SELECT INTO statement is recommended                      You must use the same name of the field of the destination table in the alias of each field of the SELECT statement                       Examples                                                                                                                                               --A  Test basic                          IF EXISTS  SELECT   FROM sys objects  WHERE OBJECT ID   OBJECT ID N  dbo   tblDestino    AND TYPE IN  N U                                DROP TABLE tblDestino                          CREATE TABLE tblDestino                                                       Id INT IDENTITY                              Field1 VARCHAR 10                               Field2 VARCHAR 12                               Field3 VARCHAR 11                               Field4 VARCHAR 16                               Field5 VARCHAR 5                               Field6 VARCHAR 1                               Field7 VARCHAR 1                               Field8 VARCHAR 6                               Field9 VARCHAR 6                               Field10 VARCHAR 50                               Field11 VARCHAR 50                               Field12 VARCHAR 50                                                     INSERT INTO dbo tblDestino                                                        Field1                                Field2                                Field3                                Field4                                Field5                                Field6                                Field7                                Field8                                Field9                                Field10                                Field11                                Field12                                                       SELECT                                123456789    -- Field1 - varchar 10                                123456789    -- Field2 - varchar 12                                123456789    -- Field3 - varchar 11                                123456789    -- Field4 - varchar 16                                123456789    -- Field5 - varchar 5                                123456789    -- Field6 - varchar 1                                123456789    -- Field7 - varchar 1                                123456789    -- Field8 - varchar 6                                123456789    -- Field9 - varchar 6                                123456789    -- Field10 - varchar 50                                123456789    -- Field11 - varchar 50                                123456789   -- Field12 - varchar 50                          GO                        Result                          String or binary data would be truncated                        Here you get the truncation error  Then  we proceed to save the information in a global temporary table                        IMPORTANT REMINDER  You must use the same name of the field of the destination table in the alias of each field of the SELECT statement                        Process                           IF OBJECT ID  tempdb    TEMP   IS NOT NULL DROP TABLE   TEMP                         go                         SELECT                                Field1     123456789                                  Field2     123456789                                  Field3     123456789                                  Field4     123456789                                  Field5     123456789                                  Field6     123456789                                  Field7     123456789                                  Field8     123456789                                  Field9     123456789                                  Field10     123456789                                  Field11     123456789                                  Field12     123456789                            INTO   TEMP                      Result                       1 row s  affected                       Test                          EXEC dbo GetFieldStringTruncate  SourceTableName      TEMP    TargetTableName    tblDestino                       Result                            12 row s  affected                          ORIGEN Nombre Campo        ORIGEN Maximo Largo  DESTINO Nombre Campo     DESTINO Tipo de campo   Evaluaci  n                         -------------------------- -------------------- ------------------------ ----------------------- -------------------------                         Field1                     9                    02 - Field1              VARCHAR 10                                       Field2                     9                    03 - Field2              VARCHAR 12                                       Field3                     9                    04 - Field3              VARCHAR 11                                       Field4                     9                    05 - Field4              VARCHAR 16                                       Field5                     9                    06 - Field5              VARCHAR 5               possible field with error                         Field6                     9                    07 - Field6              VARCHAR 1               possible field with error                         Field7                     9                    08 - Field7              VARCHAR 1               possible field with error                         Field8                     9                    09 - Field8              VARCHAR 6               possible field with error                         Field9                     9                    10 - Field9              VARCHAR 6               possible field with error                         Field10                    9                    11 - Field10             VARCHAR 50                                       Field11                    9                    12 - Field11             VARCHAR 50                                       Field12                    9                    13 - Field12             VARCHAR 50                                                                                                                                              ------------------------------------------------------------------------------------------------------------      Responsible     Javier Pardo      Date            October 19 2018     WB tests        Javier Pardo       ------------------------------------------------------------------------------------------------------------      ALTER PROCEDURE dbo GetFieldStringTruncate        SourceTableName AS VARCHAR 255         TargetTableName AS VARCHAR 255    AS BEGIN     BEGIN TRY          DECLARE  colsUnpivot AS NVARCHAR MAX                colsUnpivotConverted AS NVARCHAR MAX               query  AS NVARCHAR MAX           SELECT  colsUnpivot   stuff                       SELECT DISTINCT       QUOTENAME col NAME                      FROM tempdb sys tables tab                     INNER JOIN tempdb sys columns col                         ON col object id   tab object id                     INNER JOIN tempdb sys types typ                         ON col system type id   TYP system type id                     WHERE tab NAME    SourceTableName                     FOR XML path                            1  1                        colsUnpivotConverted   stuff                       SELECT DISTINCT        CONVERT VARCHAR MAX      QUOTENAME col NAME       AS     QUOTENAME col NAME                      FROM tempdb sys tables tab                     INNER JOIN tempdb sys columns col                         ON col object id   tab object id                     INNER JOIN tempdb sys types typ                         ON col system type id   TYP system type id                     WHERE tab NAME    SourceTableName                     FOR XML path                            1  1                --https   stackoverflow com questions 11158017 column-conflicts-with-the-type-of-other-columns-in-the-unpivot-list         IF OBJECT ID  tempdb    TablaConMaximos   IS NOT NULL DROP TABLE   TablaConMaximos          set  query               SELECT u d AS colname  MAX LEN u data   as  maximo largo              INTO   TablaConMaximos             FROM                                SELECT      colsUnpivotConverted                     FROM      SourceTableName                   T             UNPIVOT                                data                 for d in      colsunpivot                    u              GROUP BY u d           PRINT  query          exec sp executesql  query           ------------------------------------------------------------------------------------------------------------         SELECT -- Nombre de campo    RIGHT  00    ISNULL CONVERT VARCHAR col column id      2      -     col name                   --   Tipo de campo    ISNULL CONVERT VARCHAR upper typ name               ISNULL CONVERT VARCHAR col max length                         ORIGEN Nombre Campo    tcm colname                ORIGEN Maximo Largo    tcm maximo largo                DESTINO Nombre Campo    DESTINO  Nombre de campo                 DESTINO Tipo de campo    DESTINO  Tipo de campo                 Evaluaci  n    CASE WHEN DESTINO maximo largo  lt  tcm maximo largo THEN  possible field with error  ELSE    END             --             FROM tempdb sys tables tab             INNER JOIN tempdb sys columns col                 ON col object id   tab object id             INNER JOIN tempdb sys types typ                 ON col system type id   TYP system type id             RIGHT JOIN                                        SELECT column id                            Nombre de campo    RIGHT  00    ISNULL CONVERT VARCHAR col column id      2      -     col name                                  Tipo de campo    ISNULL CONVERT VARCHAR upper typ name               ISNULL CONVERT VARCHAR col max length                                       maximo largo    col max length                            colname    col name                     FROM sys tables tab                         INNER JOIN sys columns col                             ON col object id   tab object id                         INNER JOIN sys types typ                             ON col system type id   TYP system type id                     WHERE tab NAME    TargetTableName                   AS DESTINO                     ON col name   DESTINO colname             INNER JOIN   TablaConMaximos tcm                 ON tcm colname   DESTINO colname          WHERE tab NAME    SourceTableName             AND typ name LIKE   char           ORDER BY col column id      END TRY     BEGIN CATCH         SELECT  Internal error ocurred  AS Message     END CATCH     END   For now only supports the data types CHAR  VARCHAR  NCHAR and NVARCHAR  You can find the last versi  n of this code in the next link below and we help each other to improve it  GetFieldStringTruncate sql   https   gist github com jotapardo 210e85338f87507742701aa9d41cc51d

User · Answer

This can be a challenging error   Here are some notes taken from https   connect microsoft com SQLServer feedback details 339410  look for AmirCharania s comment   I ve adjusted the answer given by AmirCharania for data selected into an actual table  instead of a temp one  First select your dataset into a development table then run the following   WITH CTE Dev AS       SELECT C column id          ColumnName   C NAME          C max length          C user type id          C precision          C scale          DataTypeName   T NAME     FROM sys columns C     INNER JOIN sys types T ON T user type id   C user type id     WHERE OBJECT ID   OBJECT ID  YOUR TARGET TABLE NAME HERE  WITH SCHEMA              CTE Temp AS       SELECT C column id          ColumnName   C NAME          C max length          C user type id          C precision          C scale          DataTypeName   T NAME     FROM sys columns C     INNER JOIN sys types T ON T user type id   C user type id     WHERE OBJECT ID   OBJECT ID  YOUR TEMP TABLE NAME HERE  WITH SCHEMA         SELECT   FROM CTE Dev D FULL OUTER JOIN CTE Temp T ON D ColumnName   T ColumnName WHERE ISNULL D max length  0   lt  ISNULL T max length  999

User · Answer

For the others  also check your stored procedure  In my case in my stored procedure CustomSearch I accidentally declared not enough length for my column  so when I entered a big data I received that error even though I have a big length on my database  I just changed the length of my column in my custom search the error goes away  This is just for the reminder  Thanks

User · Answer

This error is thrown when the column of a table puts constraint   mostly length      E g  if database schema for column myColumn is CHAR 2   then when your call from any of your application to insert value  you must pass String of length two   The error basically says it  string of length three and above is inconsistent to fit the length restriction specified by database schema  That s why SQL Server warns and throws data loss  Truncation error

User · Answer

The issue is quite simple  one or more of the columns in the source query contains data that exceeds the length of its destination column  A simple solution would be to take your source query and execute Max Len  source col    on each column  I e     Select Max Len TextCol1         Max Len TextCol2         Max Len TextCol3             From       Then compare those lengths to the data type lengths in your destination table  At least one  exceeds its destination column length   If you are absolutely positive that this should not be the case and do not care if it is not the case  then another solution is to forcibly cast the source query columns to their destination length  which will truncate any data that is too long    Select Cast TextCol1 As varchar             Cast TextCol2 As varchar             Cast TextCol3 As varchar                 From

User · Answer

I was using empty string    on on table creation and then receiving error  Msg 8152  String or binary data would be truncated  on subsequent update  This was happening due to the update value containing 6 characters and being larger than the column definition anticipated  I used  SPACE  to get around this only because I knew I would be updating in bulk following the initial data creation i e  the column was not going to remain empty for long   SO BIG CAVEAT HERE  This is not a particularly slick solution but is useful in the case where you are pulling together a data set e g  for one-off intelligence requests where you are creating a table for data mining  applying some bulk processing interpretation and storing before and after results for later comparison mining  This is a frequent occurrence in my line of work   You can initially populate using the SPACE keyword i e       select             Table1  column1             Table1  column2             SPACE 10  as column name     into table you are creating     from Table1     where       Subsequent updates to  column name  of 10 characters or less  substitute as applicable  will then be allowed without causing truncate error  Again  I would only use this in scenarios similar to that described in my caveat

User · Answer

Yes I am also face these kind of problem    REMARKS VARCHAR 500  to REMARKS VARCHAR 1000    Here  I ve change REMARKS filed length from 500 to 1000

User · Answer

SQL Server 2019 will finally return more meaningful error message       Binary or string data would be truncated    error message enhancments      if you have that error  in production   it s not obvious to see which column or row this error comes from  and how to locate it exactly     To enable new behavior you need to use DBCC TRACEON 460   New error text from sys messages   SELECT   FROM sys messages WHERE message id   2628      2628     String or binary data would be truncated in table       ls     column       ls     Truncated value        ls       String or Binary data would be truncated  replacing the infamous error 8152     This new message is also backported to SQL Server 2017 CU12  and in an upcoming SQL Server 2016 SP2 CU   but not by default  You need to enable trace flag 460 to replace message ID 8152 with 2628  either at the session or server level       Note that for now  even in SQL Server 2019 CTP 2 0 the same trace flag 460 needs to be enabled  In a future SQL Server 2019 release  message 2628 will replace message 8152 by default      SQL Server 2017 CU12 also supports this feature   Improvement  Optional replacement for  String or binary data would be truncated  message with extended information in SQL Server 2017     This SQL Server 2017 update introduces an optional message that contains the following additional context information   Msg 2628  Level 16  State 6  Procedure ProcedureName  Line Linenumber String or binary data would be truncated in table     ls   column     ls   Truncated value      ls         The new message ID is 2628  This message replaces message 8152 in any error output if trace flag 460 is enabled    db lt  fiddle demo       ALTER DATABASE SCOPED CONFIGURATION       VERBOSE TRUNCATION WARNINGS     ON   OFF        APPLIES TO  SQL Server  Starting with SQL Server 2019  15 x   and Azure SQL Database      Allows you to enable or disable the new String or binary data would be   truncated error message  SQL Server 2019  15 x  introduces a new  more   specific error message  2628  for this scenario       String or binary data would be truncated in table     ls   column    ls   Truncated value      ls        When set to ON under database compatibility level 150  truncation   errors raise the new error message 2628 to provide more context and   simplify the troubleshooting process       When set to OFF under database compatibility level 150  truncation   errors raise the previous error message 8152       For database compatibility level 140 or lower  error message 2628   remains an opt-in error message that requires trace flag 460 to be   enabled  and this database scoped configuration has no effect

[sql-server] SQL Server String or binary data would be truncated

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005

Examples related to migration

Examples related to data-migration