SQL - Insert Record Error Trapping

[imnotso]

I'm in SQL 2000 running a query. I am inserting a lot of record from 1 table to another. A record on the input table doesn't match the destination table format. I'm getting the following. Server: Msg 8152, Level 16, State 9, Line 106 String or binary data would be truncated. The statement has been terminated. How do I get Query analyzer to tell me which record from the source table is in causing my problem. Should I Try...Catch? Not sure this works in SQL2000. Any help welcome.

[Blue_Boy]

check fields which have datatype varchar and set length to 255 or size which you need.

---

I Love SQL

[imnotso]

Hi thanks, there are many fields VARCHAR and INT datetime and such, It may not be the Varchar that's the problem. I really need some SQL to allow me to trap the record in error and amend the record, not the table. H.

[Michael Potter]

I know of no error trapping (although there might be some low level undocumented stuff) for identifying the row within the set that caused the error. It might not even be very helpful if it is the first of 1 million errors within the set anyway. Visually compare the sizes of the all varchar (and binary, if any) columns between the table and your input query. At least one of the columns of the input query has a column size that can be larger than is accepted by the table it is being input into. You can then run a check on the query to find which rows are offending.

SELECT
    PrimaryKey,
FROM
    WhatEverSource
WHERE
    LEN(VarCharColumn) > 25

You can also force the truncation during input which would eliminate the error but, lose some data.

[imnotso]

That's great mike..... I hate SQL for this reason, it's like something from the 1970's (**this will get them SQL fans going** ;P ) Why can it not say 'I cannot insert...' and then show me the row !! How simple would that be ?? I do some checking, thanks again...

[Michael Potter]

You are thinking that SQL is a proceedural language. It is a set based language. It identifies that the set operation failed and the reason why. What seems easy on the surface may be extremely difficult to implement when the queries get highly convoluted. Example: Which row should the error system identify when the source set is composed of a 5 way join and 3 correlated subqueries? On your side of the fence: I would like SQL to identify the column name that rejected the input data because it is to long.