jamesxshaw jamesxshaw -4 years ago 89
SQL Question

Why does SQL MERGE statement produce either truncation error or overflow error?

Imagine the following tblVendor table:

SourceSystemID (FK, tinyint, not null)
VendorIDInt (int, null)
VendorID (varchar(255), not null)
VendorName (varchar(50), null)


Here's a block that attempts to merge a SOURCE TABLE with my tblVendor table (utilizing the tblSourceSystem table):

MERGE tblVendor as TARGET
USING SYN_VENDOR_A as SOURCE
ON TARGET.SourceSystemID = CAST(ISNULL((select SourceID from tblSourceSystem where SourceCode like SOURCE_SYSTEM), 0) as tinyint)
AND TARGET.VendorID = SOURCE.VENDOR_KEY

WHEN MATCHED AND (SOURCE_SYSTEM = 'REG') THEN
UPDATE
SET
SourceSystemID = CAST(ISNULL((select SourceID from tblSourceSystem where SourceCode like SOURCE_SYSTEM), 0) as tinyint),
VendorIDInt = CAST(VENDOR_KEY as int),
VendorID = VENDOR_KEY,
VendorName = VENDOR_NAME

WHEN NOT MATCHED AND (SOURCE_SYSTEM = 'REG') THEN
INSERT (
SourceSystemID,
VendorIDInt,
VendorID,
VendorName
)
VALUES (
(SELECT CAST(ISNULL(SourceID, 0) as tinyint) from tblSourceSystem where SourceCode like SOURCE_SYSTEM), --varchar
CAST(VENDOR_KEY as int), --varchar attemping to convert to an int
VENDOR_KEY, --varchar
VENDOR_NAME --varchar
);


The tblVendor table was originally designed to where the SOURCE's VENDOR_KEY, a varchar, is always cast to an int prior to the data coming across. This isn't sustainable now because VENDOR_KEY's varchar overflowed the VendorIDInt column


Msg 248, Level 16, State 1, Line 524

The conversion of the varchar value '89617719042' overflowed an int column.


First try, I attempted to change the VendorIDInt column type to bigint and then cast VENDOR_KEY as bigint. This didn't work as an error showed


Msg 8152, Level 16, State 14, Line 524

String or binary data would be truncated. The statement has been terminated.


Second try, I altered the tblVendor to add the VendorID varchar type column while commenting out the lines in the above merge statement involving casting and VendorIDInt. My thinking was, why even use the VendorIDInt column when I could just bring data across to the new varchar VendorID column? This also didn't work


Msg 8152, Level 16, State 14, Line 524

String or binary data would be truncated. The statement has been terminated.


I received the same error.

Third try, I ran out of ideas and simply ran the above merge statement as is with nothing commented out attempting to extract the data with both VendorIDInt and VendorID columns left in there. Yet again, same truncation error. No surprise there.

Why is this same truncation error appearing over and over?

The ultimate goal is to get data from the source table to my table and either the int column overflow error shows up or the truncation error shows up. I'm stuck in what seems like an impossible situation.

What other paths forward are there? Thanks.

Answer Source

The reason why the truncation error appeared is because of the synonym table's VENDOR_NAME column containing records with more than 50 characters.

The error was gone after using the following:

LEFT(VENDOR_NAME, 50)

The approach I used to solve the issue was by commenting out and isolating each column one by one and running the MERGE repeatedly.

It's a brute force method, I know. Feel free to post a more efficient strategy when faced with such an issue if anybody has one.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download