Sql-server – VarChar to NVarChar causing truncate error

sql serversql-server-2008-r2truncate

I am importing some data from a temporary table which has a column defined as VARCHAR(150). The destination tables column is defined as NVARCHAR(200). I'm using an INSERT INTO statement, but when I run the SQL statement commenting out this column the query runs correctly. When I uncomment this line, I get:

Msg 8152, Level 16, State 4, Line 26

String or binary data would be truncated.

The statement has been terminated.

Any ideas as to why this is happening?

INSERT INTO table (col1, col2)
SELECT column1
      ,column2
  FROM tmpTable

col1 is defined as NVARCHAR(200) and col2 is a DateTime.
column1 is defined as VARCHAR(150) and column2 is a DateTime.

When I comment out column1 and col1 the import statement runs fine.

Best Answer

Well this won't happen unless

a trigger is firing on the target table, and the error happens there
the INSERT does some processing that adds enough data to make it too wide

Also, what happens with this please? This can not fail unless there is some processing.

INSERT INTO table (col1, col2)
SELECT LEFT(column1, 200)
      ,column2
  FROM tmpTable

Related Solutions

SQL Server – Identifying Columns Responsible for ‘String or Binary Data Would Be Truncated’

~~No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.~~

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:

binary or string data would be truncated => error message enhancments

And now it has been delivered in the following versions:

In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.

Here is an example:

USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));

INSERT dbo.x(a) VALUES('foo');
GO

Result in all supported versions prior to SQL Server 2019:

Msg 8152, Level 16, State 30, Line 5
String or binary data would be truncated.
The statement has been terminated.

Now, on SQL Server 2019 CTPs, with the trace flag enabled:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);

Result shows the table, the column, and the (truncated, not full) value:

Msg 2628, Level 16, State 1, Line 11
String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
The statement has been terminated.

Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.

SQL Server 2012 – Indexing on Multiple NVARCHAR(MAX) Columns

One thing you might try is breaking those fields out into their own table(s). Then reference them by ID. You could then set up a UNIQUE index on those ID columns, and still use your hashing trick to ensure you have unique values in the new tables (if the values need to be unique there).

Example table(s):

[Shipper]
 ShipperID INT,
 ShipperName NVARCHAR(MAX)

[Consignee]
 ConsigneeID INT,
 ConsigneeName NVARCHAR(MAX)

ETC...

Unfortunately, with this type of design problem you have to get a little creative. Having that many NVARCHAR(MAX) columns is problematic on many levels.

Best Answer

Related Solutions

SQL Server – Identifying Columns Responsible for ‘String or Binary Data Would Be Truncated’

SQL Server 2012 – Indexing on Multiple NVARCHAR(MAX) Columns

Related Question