Insert statement failed because of Binary data

Question

Hi,
I'm using following Insert Statement but getting an error for STRING or Binary data would be truncated.
I worked around but still throwing an error.

INSERT INTO tblIC (CUSTNO, ITMCLSS, ITEM, CUSTPART, PRICE, PDATE, ADDUSER, ADDDATE, EXPIRESON, LCKUSER, LCKDATE,RECDEL)
SELECT 'F036', ITMCLSS, ITEM, CUSTPART, PRICE, PDATE, 'Jin', GETDATE(), EXPIRESON, 'Jin', GETDATE(), 0
FROM HQ

Answer 1

Here is the example why it throws an error:

CREATE TABLE tbl1 (
 Col1 varchar(20),
 Col2 varchar(10)
);
CREATE TABLE tbl2 (
 Col1 varchar(20),
 Col2 varchar(20)
);
INSERT INTO tbl2 VALUES
('Test1', 'Test1Test1Test1');

INSERT INTO tbl1 (Col1, Col2)
SELECT Col1, Col2 FROM tbl2;

Msg 8152, Level 16, State 30, Line 19
String or binary data would be truncated.

The issue is that the varchar length of the column tbl2.Col2 is bigger than that of tbl1.Col2.

Answer 2

The error message means that you are trying to cram more characters (or bytes) into a column than the column has space for. For instance, trying to put 40 characters into a varchar(30) column.

If you are on relatively recent CUs of SQL 2016 and SQL 2017, you can run DBCC TRACEON(460) and you will get an error message that includes the truncated value. (On SQL 2019, you get this enhanced error message by default.)

Answer 3

Hi @Jinal Contractor ，

The reason for this error is: the length of the column definition in the table is smaller than the length of the actual field to be inserted.To solve this problem, you need to find a column whose length is less than the content to be inserted, and modify the length.Guoxiong’s example vividly illustrates the cause of the error.

The following code can find the length of each column, and then you can compare the lengths of the columns in the two tables to find the columns in tblIC that are smaller than the columns in HQ. After modifying these short-length columns in the tblIC table, you can successfully insert data：

SELECT Len('F036'), Len(ITMCLSS), Len(ITEM), Len(CUSTPART), Len(PRICE), Len(PDATE), Len('Jin'), GETDATE(), Len(EXPIRESON), Len('Jin'), GETDATE(), 0  
FROM HQ  

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.