我写了一个有用的存储过程来帮助识别和解决使用INSERT SELECT语句时的文本截断问题(字符串或二进制数据将被截断)。它只比较字段CHAR, VARCHAR, NCHAR和NVARCHAR，并在可能导致错误的情况下逐字段返回一个评估字段。

EXEC dbo.GetFieldStringTruncate SourceTableName, TargetTableName

这个存储过程针对的是在执行INSERT SELECT语句时的文本截断问题。 此存储过程的操作取决于用户之前是否识别出有问题的INSERT语句。然后将源数据插入到全局临时表中。建议使用SELECT INTO语句。 必须在SELECT语句的每个字段的别名中使用目标表字段的相同名称。

函数代码:

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 < 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

目前只支持数据类型CHAR, VARCHAR, NCHAR和NVARCHAR。你可以在下面的链接中找到这段代码的最后一个versión，我们会互相帮助来改进它。GetFieldStringTruncate.sql

https://gist.github.com/jotapardo/210e85338f87507742701aa9d41cc51d

您需要发布源表和目标表的表定义，以便我们找出问题所在，但底线是源表中的一列比目标列大。这可能是您正在以一种您没有意识到的方式更改格式。要弄清楚这一点，您要使用的数据库模型也很重要。

这里有一个稍微不同的答案。您的列名和长度可能都匹配，但可能您在SELECT语句中以错误的顺序指定了列。假设tableX和tableY的列名称相同，但顺序不同

至少将数据类型nchar(10)的长度更改为nchar(255)。

我在表创建上使用空字符串，然后在后续更新中接收错误'Msg 8152，字符串或二进制数据将被截断'。发生这种情况是因为更新值包含6个字符，并且比预期的列定义大。我使用“SPACE”来解决这个问题，只是因为我知道我将在初始数据创建后批量更新，即列不会长时间保持空。

这里有个大警告:这不是一个特别巧妙的解决方案，但在你把一个数据集放在一起的情况下很有用，例如，对于一次性的情报请求，你要为数据挖掘创建一个表，应用一些批量处理/解释，并存储结果前后，以便以后比较/挖掘。这是我这一行经常发生的事。

最初可以使用SPACE关键字进行填充。

    select 
           Table1.[column1]
          ,Table1.[column2]
          ,SPACE(10) as column_name
    into table_you_are_creating
    from Table1
    where ...

随后将允许对“column_name”进行10个字符或更少的后续更新(如适用可替换)，而不会导致截断错误。同样，我只会在类似于我的警告中描述的场景中使用它。

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. :)