Error during Insert a row in table

Question

Hi, I am trying to add a single row in existing "Employee" table. Looks like something issue is in length of any field etc, The insert command has more than 40 columns. Is there any way to identify which column/columns has data issue for insert otherwise I will have to use one by one column insert in DB to investigate but it will take too much time.

Error:
String or binary data would be truncated.

Answer 1

Hi,@Kenny Gua
How about add OPTION (QUERYTRACEON 460) after your insert code.
Please refer to this article for more details：How to fix the error “String or binary data would be truncated”

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

because dbo.CoolPeople doesn't exist.

If the table don't exists, then you can not use it in any SQL statement.

Answer 3

I have SQlServer-2012 and not able to run the following query because dbo.CoolPeople doesn't exist.

When you are pointed to an article, you will need to read them a little more than just glancing at them. Fact is, Brent has a CREATE TABLE statement for the CoolPeople table in his article.

Anyway, since you are on SQL 2012 you are out of luck. This error message has been improved to include the column and the value, but this improvement is only available in SQL 2016 and up. So that QUERYTRACEON is not going to help you.

So you will have to do it the boring way - taking out one column at time replacing it with a fixed value until the query does not break. Wrap the query in a transaction, so you don't insert the wrong data:;

BEGIN TRANSACTION
go
INSERT ....
   SELECT ...
go
IF @@trancount > 0 ROLLBACK TRANSACTION

Answer 4

I have SQlServer-2012 and not able to run the following query because dbo.CoolPeople doesn't exist. How I can do in SQl- Server 2012?

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)
VALUES ('Baby', '2006 Subaru Impreza WRX GD')
OPTION (QUERYTRACEON 460);
GO
--Invalid object name 'dbo.CoolPeople'.