debugsqlMinor

How to know which record caused an error

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

errorknowrecordhowwhichcaused

Problem

I have a table named TEMP1 with 5 columns that are all NVARCHAR(100)

I want to transfer the contents of TEMP1 to a table named FINAL, which has the same columns, but this time with targeted data types (DECIMAL, BIT, INT, etc.).

Right now, I do the following:

insert into FINAL
select * from TEMP1

Due to the implicit casting from nvarchar to another type, the statement may fail. If it fails, is there a way to know which line(s) caused the error? I was thinking about doing a WHILE loop to identify the lines in error. Is there a better/more efficient way?

Solution

SQL itself doesn't provide any way to do this, an INSERT will always entirely succeed or entirely fail (with one caveat, see #7 below).

So you'll need to do one of the following:

Update/cleanse the data in the source table in advance

Filter the rows to insert, something like WHERE LEN(longfield)

Properly transform it on the fly: LEFT(longfield,30) will chop off any text that is too long, or use one of the new SQL 2012 functions like TRY_CONVERT, as suggested by Jonathan Fite in the comments.




Insert it one row at a time, using a TRY/CATCH block, and either fix the problem, or log the bad row for action later on



Insert one row at a time, but instead of taking action, just ignore the error on each row, and compare the two tables at the very end.



This assumes you have some key column you can join:

SELECT TEMP1.*
 FROM TEMP1
 LEFT OUTER JOIN FINAL
 ON TEMP1.Rowid = FINAL.Rowid
 WHERE Final.Rowid IS NULL


SSIS provides a number of tools and options for data transformation and error catching if you're doing large-scale migrations. You can pipe the error rows into a second table, for example.



This won't help you with data conversion errors, but there is one specific kind of error you can "skip and continue": if the error is produced by a unique constraint (a special kind of index that prevents you from entering duplicate data into a table, based on whatever field or set of fields you specify).



This particular constraint can be created with a parameter of

IGNORE_DUP_KEY = ON. When this is set to ON only the rows that violate the constraint will fail, the remaining rows will be properly inserted. When it is set to OFF` the entire batch will fail.

Code Snippets

SELECT TEMP1.*
 FROM TEMP1
 LEFT OUTER JOIN FINAL
 ON TEMP1.Rowid = FINAL.Rowid
 WHERE Final.Rowid IS NULL

Context

StackExchange Database Administrators Q#166001, answer score: 4

Revisions (0)

No revisions yet.