When doing an insert, sourced by a select with numerous columns, and experiencing the 'string or binary data would be truncated', is there a quicker/better way or trick to identify which column in the Select statement (insert) contains a 'too-long' value, other than, the quickest way I have come up with but it still seems awfully tedious:
In a new testing block of code, structure the select statement that was sourcing the query as a derived query (or CTE), and then FROM that, selecting each column, one at a time, where len(Col) is greater than [the length of the destination table column).
Is there any trick? Has someone written some really wonderful procedural stuff that would do this for me?
I know you may laugh, but in MS Access I could have written a VBA proc in 2 minutes that would have done this, please tell me there is something in T-SQL to serve as a debugging type of tool for this scenario?
Are you inserting one row at a time? Then you could compare the data lengths of your source data and target columns.
e.g.
declare @a varchar(300) = 'too long ' + replicate('.', 200);
select case when DATALENGTH(@a) > max_length then 'oops' else 'ok' end
from sys.columns where object_id = object_id(N'yourtablet', 'U')
and name = 'yourcolumn'
gbritton - thank you, in this case I am not but I will keep that trick in mind - and store it for possible use. Thank you.
Scott, I did that, and then I examined the resulting table. So I'm assuming that the resulting table that gets created automatically gets created with each column being the length of the max length that it had to deal with while it was being created, that's the point right? OK so I reviewed the columns, and there is one column, prov_id, where it created the column as varchar(1000). However, I swear, I've reviewed the data set from this select statement, and the distinct lengths of the resulting prov_id's are 6, 7,and 8.
Can you think of any mistake I may be making here? Very flummoxed at this point.
HA HA - oh wowzers. That's funny.
I finally did find that I was making a mistake. The column from the source table for middle initial had a few long values. Unbelievable.
Thank you everyone for looking. It would be cool to code some kind of tool that accepts a resultset or table variable as a parameter and then checks for this information.
Scott, are you hinting that the offer of sharing such a useful function is forthcoming?
On the other hand, it would be extremely useful for me to write it myself, (educational value), so I wonder if you could just point me in the general direction of the technique used .. and I can give it a shot?
One thing I'm not at all familiar with is querying system tables / system objects. It's the syntax that often gets me, plus just not knowing what's available for querying.
Yes, I wasn't connecting the two, sorry my mistake.
When you posted it I was trying to think of how it might be implemented, in the form of a tool that could be re-used and shared. I'm not really too sure ... Would this be in the form of a cursor then? and your code operates on each individual insert, as it goes along? Presumably until it finds the offending column, then exits, for the sake of efficiency?
The thing I have no idea how to do is use T-SQL alone to create "something" (function? sproc?) that accepts a dataset (a View or Select or Table or Table var) as a parameter .
In other words I'd like to create a tool that I can pass to someone else and say, using this function, pass in the input Select statement and the destination table, and the function will return the first conflict (datatype, length, etc) that it comes across.
Is that even possible to do with T-SQL alone, without using some OOP - vb.net, c# etc?
On the Connect page there are couple of ideas along the same lines as suggested by @gbritton - for example, look for the post by AmirCharania on 2/17/2015 at 10:48 AM in the comments section. Admittedly, the ideas/workarounds are not painless; more like low-dose aspirin for a mighty severe headache - i.e., it may or may not help.
