Error during Insert a row in table

Kenny Gua 411 Reputation points
2022-01-24T01:36:31.187+00:00

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.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. LiHong-MSFT 10,046 Reputation points
    2022-01-24T05:47:31.167+00:00

    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.

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
  1. Olaf Helper 40,656 Reputation points
    2022-01-24T20:40:57.233+00:00

    because dbo.CoolPeople doesn't exist.

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

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 100.8K Reputation points MVP
    2022-01-24T23:21:15.803+00:00

    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
    
    1 person found this answer helpful.
    0 comments No comments

  3. Kenny Gua 411 Reputation points
    2022-01-24T18:58:57.597+00:00

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