Explanation of SQL Server Error Message - "String or binary data would be truncated"

The error message "String or binary data would be truncated"  tells we are trying to put a longer string into a column of smaller size.  The destination column size is smaller than the source column size.
e.g
Source Column - Product CHAR(4)|
Destination Column - Product CHAR(3)

We have prepared the below example (TSQL script) which will help you in finding which record is casuing this error. We are using two tables :

1) Tbl_Character: For Error free records which is having column [TextString] of length  [char](3). This is our Target table.

2) [Tbl_Character_Error]: For records having error which is having column [TextString] of length  [char](10). This table is used to capture those records which are having length greater than char(3).

The idea is when we execute the below statement. All the records from source having length <= Char(3) should insert into our target table and  All the records from source having length > Char(3) should insert into error table

/* Creating tables */
CREATE TABLE [dbo].[Tbl_Character] ([TextString] [char](3) NULL)
GO

CREATE TABLE [dbo].[Tbl_Character_Error] ([TextString] [char](10) NULL)

/* Declaring Variables */
DECLARE @COUNT INT
/* Setting the value */
DECLARE @MIN INT = 1
/* Declaring Table variable */
DECLARE @TABLE TABLE (
	ID INT IDENTITY
	,TextString CHAR(4)
	)

/* Populating Data */
INSERT INTO @TABLE
SELECT 'ABC' AS TextString

UNION

SELECT 'DEF'

UNION

SELECT 'GHI'

UNION

SELECT 'JKL'

UNION

SELECT 'MNOP'

UNION

SELECT 'QRS'

UNION

SELECT 'TUVW'

UNION

SELECT 'XYZ'

/* Getting Row count*/
SELECT @COUNT = @@ROWCOUNT

/* Starting the loop*/
WHILE @COUNT > @MIN
BEGIN
	BEGIN TRY
		/* Populating data which dont have error */
		INSERT INTO [dbo].[Tbl_Character]
		SELECT TextString
		FROM @TABLE
		WHERE ID = @MIN

		SET @MIN = @MIN + 1
	END TRY

	BEGIN CATCH
		BEGIN TRY
			/* Populating data which is having error */
			INSERT INTO [dbo].[Tbl_Character_Error]
			SELECT TextString
			FROM @TABLE
			WHERE ID = @MIN

			SET @MIN = @MIN + 1
		END TRY

		BEGIN CATCH
			SELECT @@ERROR
		END CATCH
	END CATCH
END

'MNOP' & 'TUVW' are greater than char(3) length hence these two records casuing the "String or binary data would be truncated" error.