3

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?

6
  • Please provide the exact error, in it's entirety, along with column type for the temp and final tables. Mar 2, 2017 at 19:15
  • 2
    Look at Try_CONVERT. It returns NULL when the input type cannot be converted to the destination type. msdn.microsoft.com/en-us/library/hh230993.aspx Mar 2, 2017 at 19:16
  • @Kris Gruttemeyer: This is a more of a general question. An error could be, for example, error casting nvarchar to decimal, or any other casting error Mar 2, 2017 at 19:19
  • Depending on your requirements, you could either use TRY_CONVERT directly to populate your columns (leaving them NULL where conversion isn't possible), or filter out rows that cannot be converted (WHERE TRY_CONVERT(decimal(10,2), COLUMNA IS NULL OR ...), possibly saving them elsewhere for manual conversion/data cleaning.
    – RDFozz
    Mar 2, 2017 at 19:33
  • @RDFozz good idea. Post this an a anwer, I'll accept it. Mar 2, 2017 at 19:44

2 Answers 2

4

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:

  1. Update/cleanse the data in the source table in advance
  2. Filter the rows to insert, something like WHERE LEN(longfield)<30 will only try to insert rows short enough for the destination field
  3. 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.
  4. 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
  5. 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
  1. 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.
  2. 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.

1

Moved from comments to answer, at OP's suggestion:

An alternate option to @BradC's suggestions - actually, a variation on his option 3:

If inserting NULL values for the values you can't convert (using TRY_CONVERT) isn't an acceptable option, you could first identify the rows that would fail (SELECT <your current query> WHERE (TRY_CONVERT(decimal(10,2), COLUMNA IS NULL OR ...)). These rows could be moved to another table, where they could either be manually entered/converted, or where the data could be cleaned, then moved back to the original table to be processed normally.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.