1

I am trying to insert data into a temp table from a select query. I have almost 100 columns in my select query. How would I know or is there any trick by which I could know that a specific column is causing the binary data truncate error?

My query is like:

Insert into #t1
   Select t1, t2,....
   From Table1

What I thought out is to include additional column for each of my column which will give me length of column. But issue is I will having additional 100 columns in my select. Then I have to individually check for max size for each column.

Any help or suggestions will be appreciated.

marc_s
  • 675,133
  • 158
  • 1,253
  • 1,388
Paresh J
  • 2,285
  • 3
  • 22
  • 31
  • 3
    Possible duplicate of [SQL Server String or binary data would be truncated](http://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated) – Bridge Nov 30 '15 at 09:23
  • 2
    No, not really. It doesn't even have to be one individual column - there's a limit to the size of a table row (and large rows are a terrible idea anyway). Basically, `I have almost 100 columns in my select query.` is your problem. Why? – Luaan Nov 30 '15 at 09:25
  • Unfortunately, no, SQL Server doesn't offer much help here. What I normally do is find the *smallest* `varchar` columns (those are the most likely to cause a truncation) and comment those out from my `insert` statement one by one - if the `insert` now suddenly runs, then this column was the culprit. Repeat until you find your problem column (or multiple problem columns) – marc_s Nov 30 '15 at 10:32

1 Answers1

3

Instead of doing 

Insert into #t1
Select t1, t2,....
From Table1

You can do:

Select t1, t2,....
into #t1
From Table1

And then you can check what happened with:

tempdb..sp_help #t1

That will show you the data types for each column.

James Z
  • 11,838
  • 10
  • 25
  • 41