This procedure is intended 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. SQL SERVER.

GetFieldStringTruncate.sql

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