SQL Server Error: Msg 8114, Level 16 error on Insert Statement

Question

I am puzzled by this problem. I am load a table with some data for testing and have broken the records to insert into sets of 950 records. The first 3 set loaded and then I get this error on the insert statement:

Msg 8114, Level 16, State 5, Line 2859
Error converting data type varchar to float.
I have studied the data contents but find no error. confusions is associated with the fact that the error is on the insert statement itself. Im using a script in the SSMS. relevant images of the problem are included.

I need to understand this so any helps will be appreciated

Answer 1

Coupled with the fact that the original error pointed to the Insert statement itself

Line numbers in error messages are not always accurate. There have been some improvements, but there are still cases where the error message only points to the start of the message. For this message I would absolutely not expect the error message to flag the line where the bad data is.

After applying an insert for each row, wouldn't you know it? every row went into to the table without issue.

So that was the original target table, the not a table of strings that I suggested?

I can see how it that could have worked out. Look at this:

CREATE TABLE mytable (a int, b varchar(20), c int)

INSERT mytable(a, b, c) VALUES (1, 'Alfons', 37)
INSERT mytable(a, b, c) VALUES (2, 1E0, 45)
INSERT mytable(a, b, c) VALUES (3, 'Petronella', 212)

This runs successfully. Yet this statement:

INSERT mytable(a, b, c) VALUES 
   (1, 'Alfons', 37),
   (2, 1E0, 45),
   (3, 'Petronella', 212)

Same data, but now there is an error:

Msg 8114, Level 16, State 5, Line 7
Error converting data type varchar to float.

The first statement worked because the single float value converted implicitly to varchar. But in the second statement, the varchar values were mixed with the float values, and this caused an implicit conversion from varchar to float, because of the type-precedence rules in SQL Server.

This also means that none of my suggestions so far have been very accurate. Hm, maybe this? Now that you have inserted the data into the target table, maybe this can reveal something:

SELECT * FROM tbl WHERE try_convert(float, stringcol) IS NOT NULL

That is, all possible string values that can be converted to floats are suspects. If they are only a handful, you can search for them, and see if they are missing quotes or what ever.

The other alternative is to cut down the batch of 950 to smaller batches of say 100, and there is a failing batch of 100 cut that one down into batches of 10.

When doing this, you don't need to INSERT. You can just say:

SELECT * FROM (VALUES (...), (...)) AS V(a, b, c, ...)

The list after V should have has many entries as there are columns in the VALUES lists.

Answer 2

Replace the target table with a table where all float columns are strings. Then run queries like:

SELECT * FROM #temp WHERE col IS NOT NULL and try_convert(float, col) IS NULL

to find the offending value.

Answer 3

Data on your screenshot is okay. Just check if data in seventh column is the string.

Answer 4

Hi @John Hard ,

If the data type of the data you insert is the same as the data type of the corresponding column in the table, then the inserted data you provide is correct.

You can check whether the data type of the column is the same as the data type of the inserted data.The following code can return the data type of a column：

select sql_variant_property(columnname,'basetype') from tablename  

If the problem is still not resolved, please provide the create code and inserted code of the table.

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October--https://learn.microsoft.com/en-us/answers/questions/142427/users-always-get-connection-timeout-problem-when-u.html