Sql select insert debugging

Question

I would like to find a quick method for debugging a insert-select statement. Example:

Create table tbl_int (
        tabid int identity,
        col1 bigint)

Create table tbl_char(
        tabid int identity,
        col2 nvarchar(255))


insert into tbl_char(col2) 
    select '1' union 
    select '2' union
    select 'a'

insert into tbl_int(col1)
    select col2 
    from tbl_char

Of course, the insert select above fails to run and it is obvious that 'a' cannot be converted to bigint. But what happens when I have 1 milion records in tbl_char. Is there any way of finding the source value of the error: "Error converting data type nvarchar to bigint." P.s. Using a convert or cast function and scanning the table with top until finding the right value is a little bit too expensive.

Do you want to extract the numeric part if exists or ignore totally rows with character data? — niktrs, Aug 03 '11 at 08:22
No. I just want to find the value that caused the error. In the example above 'a'. — Corovei Andrei, Aug 03 '11 at 08:24

Massimiliano Peluso · Accepted Answer · 2011-08-03T08:45:37.803

Why don't you wrap the SQL that throw the exception into a Try/Catch block to have more info about it

BEGIN TRY
    SELECT *
        FROM sys.messages
        WHERE message_id = 21;
END TRY
GO
-- The previous GO breaks the script into two batches,
-- generating syntax errors. The script runs if this GO
-- is removed.
BEGIN CATCH
    SELECT ERROR_NUMBER() AS ErrorNumber;
END CATCH;
GO

the below are all the error information you can check

ERROR_NUMBER() returns the error number.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.

ERROR_SEVERITY() returns the error severity.

ERROR_STATE() returns the error state number.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

http://msdn.microsoft.com/en-us/library/ms179296.aspx

if this is not enough you can even write a query to select all the records where certain field is not convertible to a number(in your case there is a NVarChar which is not convertible)

The following example uses ISNUMERIC to return all the postal codes that are not numeric values.

SELECT City, PostalCode
FROM Person.Address 
WHERE ISNUMERIC(PostalCode)<> 1

http://msdn.microsoft.com/en-us/library/ms186272.aspx

this is a good start but unfortunately it still doesn't help me identify the value that caused the error. I can find more info about the error but nothing about the value. — Corovei Andrei, Aug 03 '11 at 08:37
I get your point. But it is not exactly what I was expecting. An error can occur at the convertion even if you filter the data with isnumeric. I shall give you an example select convert(bigint,'1e100') where isnumeric('1e100') = 1 — Corovei Andrei, Aug 03 '11 at 08:50
have a look at :http://stackoverflow.com/questions/5828510/convert-nvarchar-to-bigint-in-sql-server-2008 you are using it wrongly. You have to use ISNUMERIC ONLY to find out all the rows that cannot be converted. — Massimiliano Peluso, Aug 03 '11 at 08:55

score 0 · Answer 2 · answered Aug 03 '11 at 16:03

Let's start with why are you trying to send data that is not the same data type to another table? If you do not want non integer data in the column why are you allowing it to be string data to begin with? So first you need to look at if you have a design issue that is affecting data integrity.

If you are doing imports from some other system and have no control over the data type the use, then you need to clean the data before doing the insert. There is no one size fits all fix for this. You will have to write code to find all data that won't meet the terms of the table you are moving it to datatype by data type and field by field. This may be much more complex than using isnumeric (which can have false positives especially if there is decimal information in there as well.) and isdate() and you may need to write functions specific to your needs. It can take a long time to get the cleaning correct. You might need to restrict the values to a certain subset or have a conversion table that converts what they put inthe table to what your system will accept. Suggest you identify the bad data first, move it to an exception table and then do the insert based on records not in the exception table.

In order to clean the data you need to find/know the 'dirty values'. OP wants to find the 'dirty values'. How can you clean your data if you don't know where the 'dirt' is? Or how do you find the 'dirt' in your data? — Jens Wagemaker, May 09 '20 at 07:43

score -1 · Answer 3 · answered Aug 03 '11 at 08:55

As you understand, the char value converted into bigint when you execute insert..select. So, we cannot avoid the converting, but we can try to avoid the second table scanning. I propose to create INSTEAD OF INSERT trigger for table tbl_int. In the body of this trigger you can convert the char value into bigint, and if you recieve the error, you can insert this char value into staging table. If there is no error when convering, you can insert this bigint value into your tbl_int table.

Sql select insert debugging

3 Answers3