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!

Answer 1

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]