SQL query error help

  • rocky_498

    SSCertifiable

    Points: 6499

    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.

  • ratbak

    SSC Journeyman

    Points: 83

    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: 6499

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

  • jcelko212 32090

    SSCrazy Eights

    Points: 9049

    >> 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: 6499

    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: 110572

    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)?:) 🙂 :)?
    Health & Nutrition
    Make Guaranteed Income

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

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