CODEWITHSUNDEEP
sql-serversql-server-2005t-sql
Dibstar
Dibstar

Reputation: 2364

identifying values which would be truncated in an insert query

I was trying to insert some data into a table and I got the error message 

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.

Now I know that this was due to trying to insert data which was too big for the column (in this case a VARCHAR(50) ), but it got me thinking about whether or not it was possible to:

a) see what specific values would be truncated (bearing in mind that this could happen for multiple columns), perhaps by giving an output of all of the rows which would be truncated?

b) if within a batch insert there is any way of seeing for each column which would be truncated the maximum input data value (so I could see if the data storage type actually needs to be changed or if it's only one or two cases which would not fit into the column?)

Upvotes: 6

Views: 9786

Answers (4)

profMamba
profMamba

Reputation: 1008

The best solution I have found is by selecting only the structure into a new table (SELECT INTO FROM WHERE 1 = 0) and comparing the columns types and lengths.Full answer here.

Upvotes: 2

Peter Kelly
Peter Kelly

Reputation: 14401

You can get the maximum length of the columns in a table as follows 

SELECT CHARACTER_MAXIMUM_LENGTH
FROM MyDatabase.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='MyTableName'

INFORMATION_SCHEMA.COLUMNS is a built-in system View. This should help with your second question above.

Upvotes: 4

John K.
John K.

Reputation: 5474

If your using fairly simple types you can try 

select datalength(mycolumn) from mytable where myid = 1

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453707

There is no particularly satisfactory way of finding the problematic rows.

This blog post has one solution. (SET ANSI_WARNINGS OFF and insert into a staging table then use EXCEPT against the source to find rows that did not import successfully)

Upvotes: 11

Related Questions