SQL query error help

Question

SQL query error help

rocky_498

SSCertifiable

Points: 6532
More actions
September 25, 2020 at 4:29 am

#3790853

Hi Guys,
I have a table, around 320 fields. I am trying to use a simple Insert statement.
Insert into a table (List all fields)
Select all fields
One of the fields from 320 is giving me "Truncation error" What is the best way to pinpoint which field has an issue. Any advice would be greatly appreciated.
December 8, 2020 at 5:48 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ratbak Ten Centuries Points: 1358 More actions · Answer 1

With SQL Server 2019, you can enable trace flag 460 to get detailed messages that identify the column and even offending value.

https://techcommunity.microsoft.com/t5/sql-server/string-or-binary-data-would-be-truncated-replacing-the-infamous/ba-p/386014

https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/

rocky_498 SSCertifiable Points: 6532 More actions · Answer 2

Thanks ratbak for your help. We are using SQL Server 2016. Let me talk to our DBA.

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

>> I have a table, around 320 fields... <<

You might want to read the SQL standards, so you'd know what the term "field" means in this language. It is a meaningful subset of values in a column, such as year, month and day fields in a column of DATE type. I would assume you probably misspoke. And you really meant 320 columns, each modeling a particular attribute with a particular data type.. But I'm curious as to what these columns mean. In over 30 years, I have only gone a little over 100 columns; the example was an individual allergy tests laid out in a grid taped off on the back of a patient. Any other time I've seen something this big. It's been a denormalized mess, or someone tried to put an entire dossier into one table without normalizing it. Could you give us a little more information?

Please post DDL and follow ANSI/ISO standards when asking for help.

rocky_498 SSCertifiable Points: 6532 More actions · Answer 4

Hi ratbak, thanks for your advice, and it works. However, it works if its a truncation error. How can I capture if my source is varchar and destination is numeric, how can I capture that?

sgmunson SSC Guru Points: 110640 More actions · Answer 5

rocky_498 wrote:

Hi ratbak, thanks for your advice, and it works. However, it works if its a truncation error. How can I capture if my source is varchar and destination is numeric, how can I capture that?

When you SELECT from a column that is varchar() and want to insert it into a numeric() column, you can always use th TRY_CONVERT() function to attempt to convert the incoming value to the appropriate numeric format, and if it wouldn't convert properly, a NULL value will appear instead. A query for NULL values in the destination where a NON-NULL value appears in the source could then easily identify the problematic values. Alternatively, just SELECT the primary key and that one source column using that function, and WHERE source_column IS NOT NULL, and then see what rows have NULL values.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)