CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Massimiliano Peluso
Massimiliano Peluso

Reputation: 26727

INSERT INTO SELECT error handling

I'm writing a T-SQL stored procedure to insert into a table using the statement 

INSERT INTO [tableName]
.....
SELECT * FROM [tableName]

Sometime the input table (SELECT) may contain wrong values: the table where inserting is expecting a decimal and the select return an NVarChar value which will throw a conversion error. Now because the statement does a "bulk insert" I do not know what is the last record inserted or what is the record that contains the wrong data. SQL only says "conversion error....." Is there any way to "log" the last inserted record so that at least I know what line contains the wrong value? (without using cursors)

Upvotes: 1

Views: 9246

Answers (5)

Jayesh Prakash
Jayesh Prakash

Reputation: 141

I have once faced this issue with importing 15 Million rows in a table. We finally decided it was best done in SSIS with following approach:

  1. Create lets say n data tasks with one following the other "ON ERROR" only
  2. In the first data task set COMMIT batch as 100K. And on error go to next one whcih we named 10K
  3. In this task, whcih is 10K, we set COMMIT batch to 10K and "ON ERROR" we go to 1K and so on and so forth.
  4. We eventually , on the 1 record COMMIT BATCH data task "ON ERROR", move the data into a table (call the ERROR RECORD table) that has enough columns (all VARCHAR/NVARCHAR) to hold incoming data + one extra column to hold SSIS Error message for that row.

Later when SSIS finished we would analyse all records that failed. This way lets say you had only 5 error records out of 15 Million, we would have ionserted 1499995 records succesfully and 5 records in quarantine table.

Thanks and hope this approach helps

Upvotes: 2

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

You can use below statement that which column is making conversion problem.

SELECT SQL_VARIANT_PROPERTY(col1, 'BaseType') from Table1

To filter the issue

SELECT * from Table1 WHERE QL_VARIANT_PROPERTY(col1, 'BaseType') not  like 'decimal%'

Upvotes: 1

TT.
TT.

Reputation: 16137

Observe the result of this query

SELECT 
    v.id,
    v.val,
    x=TRY_CAST(v.val AS DECIMAL(28,2))
FROM 
    (VALUES(1,'1.26'),(2,'A.13')) AS v(id,val) 
WHERE
    TRY_CAST(v.val AS DECIMAL(28,2)) IS NULL

Results:

+----+------+------+
| id | val  |  x   |
+----+------+------+
|  2 | A.13 | NULL |
+----+------+------+

It uses TRY_CAST, which returns NULL if the CAST failed. You can use this to determine the records that fail, or to narrow the set you insert into the target table.

Upvotes: 5

George T
George T

Reputation: 857

You can try to use the OUTPUT clause in order to output the inserted rows until it encounters the error. You can also make use of an ORDER BY so that it will be faster to determine exactly which is the next row for which the insert fails. Then, knowing the row that fails, it should be easier to determine the field with the issue.

MSDN Link

An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement.

Upvotes: 1

TomTom
TomTom

Reputation: 62093

There is no last error.

As per basic database ACID conditions, the statement is ATOMIC - it works completely or it fails completely. There also is no defined order in which rows are processed internally - this may vary depending on server and data characteristics.

Generally you sanitize data before copying it into the target table.

Upvotes: 2

Related Questions