Msg 8152, String or binary data would be truncated

Question

I have this table:

CREATE TABLE Vendors

(
    VendorID            NUMERIC(10)     NOT NULL,
    VendorName          CHAR(50)        NOT NULL,
    VendorAddress       VARCHAR(30)     NULL,
    VendorCityName      VARCHAR(20)     NOT NULL,
    VendorStateName     CHAR(2)         NOT NULL,
    VendorZip           VARCHAR(10)     NULL,
    VendorContactName   CHAR(50)        NOT NULL,
    VendorContactPhone  VARCHAR(12)     NOT NULL,
    VendorContactEmail  VARCHAR(20)     NOT NULL,
    VendorSpecialty     CHAR(20)        NOT NULL

    CONSTRAINT VendorsPK        PRIMARY KEY (VendorID)      
);

And this insert:

INSERT INTO Vendors(VendorID, VendorName, VendorAddress, 
  VendorCityName, VendorStateName, VendorZip, VendorContactName, 
  VendorContactPhone, VendorContactEmail, VendorSpecialty)
VALUES(151330, 'Hyperion', '77 West 66th Street', 'New York', 
  'NY', 10023, 'John Hinks', '212-337-6564', 
  '[email protected]', 'Popular fiction')

Why does this statement yield the 8152 error?

Answer 1

VendorContactEmail is only 20 bytes. Your e-mail address on the first line ([email protected]) is longer than that - 24 bytes. And many e-mail addresses will be longer. Who decided to only allow 20 characters in the e-mail address column? According to the standard, this should be VARCHAR(320) - 64 characters for <localpart> + 1 for @ + 255 for <domain>.

As for the error message itself, finding the culprit is easier today than it was back then.

• String or Binary data would be truncated: replacing the infamous error 8152

Answer 2

How to Force an Operation that Results in Truncation to Execute Anyway

• Do you know what truncation is?
• Do you know what is going to be truncated?
• Is it your intention to truncate long data to fit?

If and and only if you answer YES to the above questions, you can force your inserts to ignore the warning and run anyway. If you answered no to any of the above, read on.

SET ANSI_WARNINGS  OFF;
-- Your operation TSQL here.
SET ANSI_WARNINGS ON;

(source)

What is Truncation in this Context

Truncation is simply putting as much of a value as will fit into the column and then discarding any portion that doesn't fit. For example, truncating The quick brown fox jumps over the lazy dog to 13 characters would be The quick bro

Why would I receive this error message

You receive this error when you attempt to insert data that won't fit into a destination column definition because the data is too short.

I'm trying to insert many rows of data and want to determine which rows don't fit

Aaron's excellent answer notes that if you are running SQL Server 2019 or newer, you'll actually get a message that contains the column and value that won't fit - which is awesome! But if you aren't running a version that new, read on for tips.

If you receive this error message while attempting to bulk insert many rows of data, you might try splitting the insert into multiple inserts and running them separately to narrow down where the long value is.

Alternatively, you could insert the data into a new temp table and search said temp table for values that won't fit into your destination table.

--(insert into new temp table #Vendors)
INSERT INTO #Vendors(VendorID, VendorName, VendorAddress, 
  VendorCityName, VendorStateName, VendorZip, VendorContactName, 
  VendorContactPhone, VendorContactEmail, VendorSpecialty)
VALUES(151330, 'Hyperion', '77 West 66th Street', 'New York', 
  'NY', 10023, 'John Hinks', '212-337-6564', 
  '[email protected]', 'Popular fiction')

Then query for rows that don't fit.

--(query for values that don't fit)
SELECT *,
LEN(VendorContactEmail) AS Length
FROM #Vendors
WHERE LEN(VendorContactEmail) > 20 --set your destination column length is here

See also LEN and DATALENGTH documentation for information on whitespace handling and binary data lengths.