How to fix failed data type conversion error

Question

I am inserting multiple columns from one table into another with MS SQL. I imported the data from Excel into a staging table and now inserting it into another table (to get around the float data type problem with excel) I updated that data types in the staging table to match the data types in the final table.

Simplified version with only 1 column:

Insert into [MKB].[dbo].[Purchases]
(Product_Type_ID)
select
'Product_Type_ID'
from [MKB].[dbo].[test_import3]

Error message: Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Product_Type_ID' to data type int.

I have double checked and the Product_Type_ID column in the test_import3 table is listed as Int. The values in this columns are all whole numbers. Is there a way to properly convert and commit changing the data type into int to allow the insert to complete?

Thank you for your assistance!

Accepted Answer

Because of the apostrophe around "Product_Type_ID" in the SELECT part it's treaten as string, not as column name, and that string can't be converted to integer, that's why you get an error message. Remove them.

=>

Insert into [MKB].[dbo].[Purchases]
(Product_Type_ID)
select Product_Type_ID
from [MKB].[dbo].[test_import3]

=>

Insert into [MKB].[dbo].[Purchases]
(Product_Type_ID)
select Product_Type_ID
from [MKB].[dbo].[test_import3]

Share via

How to fix failed data type conversion error

0 additional answers

Your answer