INSERT converting data format when not needed

Question

INSERT converting data format when not needed

Flybridge

Grasshopper

Points: 19
More actions
December 7, 2012 at 3:33 am

#285969

I am using an INSERT INTO statement to enter data into a table. There are only 3 columns. The first two are foreign keys, the last is the actual data needed. The definition for that column is varchar(256)(not null).
When I run the statement I get an error message saying
'Conversion failed when converting the varchar value '1055BS000' to data type int.'
Why is it attempting to convert to a data type other than the one used in the column definition? And how can I stop it doing that?
Many thanks.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

nigelrivett SSCertifiable Points: 5472 More actions · Answer 1

Are you specifying the column list on the insert? If not it might not be inserting that data into the column you expect.

Otherwise maybe there is a trigger or constraint on the table which expects only integer data in the column.

Note: You probably need to include data for the foreign key columns in your insert statement.

Cursors never.
DTS - only when needed and never to control.

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

steve.bavis (12/7/2012)
I am using an INSERT INTO statement to enter data into a table. There are only 3 columns. The first two are foreign keys, the last is the actual data needed. The definition for that column is varchar(256)(not null).
When I run the statement I get an error message saying
'Conversion failed when converting the varchar value '1055BS000' to data type int.'
Why is it attempting to convert to a data type other than the one used in the column definition? And how can I stop it doing that?
Many thanks.

Post the script that you are executing and DDL for the table and someone will tell you what is wrong.

My gut feeling is that you are doing this: -

INSERT INTO yourTable(col1, col2, col3)

SELECT 1, 1, 1055BS000;

Instead of this: -

INSERT INTO yourTable(col1, col2, col3)

SELECT 1, 1, '1055BS000';

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Flybridge Grasshopper Points: 19 More actions · Answer 3

Thank you for the posts.

It seems the problem was not with the data mentioned in the error message. Because other entries were written without quotes and were number-looking it thought they should all be integers, even thought the column def is varchar.

Putting quotes around the data in other entry lines got it to work. But in that case I would have expected the error message to complain of trying to enter integers into a varchar column. Strange.