Troubleshooting: string or binary data would be truncated

Image you have an INSERT INTO query that takes a long time to run that has multiple joins and complexities where many of the columns are string types and one of these is presenting an error. The select statement takes over an hour to run and ultimately fails with the “string or binary data would be truncated” error message.

The error is that you have selected a lump of text to insert in to a column that is not big enough to take it. If SQL performed the operation only part of the text would be copied and the rest would be truncated (the end lopped off) so to protect you it aborts the operation.

e.g.

INSERT INTO e (x,y,z… many string columns)
SELECT x,y,z many string columns
FROM a
INNER JOIN b
INNE JOIN c
LEFT join d
Where g=1 AND h=2 AND k=3

You need to find out the sizes of the columns to be returned from the SELECT statement.

You could look in to each of the tables referenced and write down the sizes to cross reference with the insert table. Sounds like a lot of boring work to me.

You could try running the query with LEN() around the columns but this will still take quite a while if the joins are complex. Sounds tedious and error prone to me.

Is there an easier and safer way? Of course there is…

  1. Comment out the insert into and any where or order by clause
  2. Add in an INTO tempTable before the FROM
  3. Add a WHERE 1=0 to the end of the statement after the joins.
  4. Run the query

SELECT x,y,z many string columns
INTO tempTable
FROM a
INNER JOIN b
INNE JOIN c
LEFT join d
Where 1=0

Sql will now parse the query and allocate space for the result which will be zero rows on account of the false (1=0) WHERE condition and then save the resulting empty set to your temp hard table.
You can now script tempTable to a new query window and you will see the sizes of the string columns.
Compare these with the INSERT table.

Job done.

Advertisements

About davidbridge

I am a contractor for David Bridge Technology Limited specialising in database design and Web development using Microsoft technologies such as c#, MVC .net and SQL Server (full stack)
This entry was posted in Developer stuff, SQL Stuff and tagged , . Bookmark the permalink.

2 Responses to Troubleshooting: string or binary data would be truncated

  1. Pingback: Troubleshooting – string or binary data would be truncated » SQL Server and Beyond

  2. Al McNicoll says:

    The temporary-table insert is a very useful workaround – thanks!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s