0

I'm using Microsoft SQL Server, the version doesn't really matter, because it seems to work the same on most of the versions.

It's easier to show an example than explain:

CREATE TABLE form (
    id int primary key,
    response varchar(255) not null
);

INSERT INTO form (id, response)
VALUES (1, 1), (2, 'yes');

The 2nd statement fails with error [S0001][245] Line 6: Conversion failed when converting the varchar value 'yes' to data type int.

How does this happen? Why is SQL Server trying to convert varchar to int when writing into varchar column? This makes no sense to me.

And can I make it not throw this error without explicitly converting int to varchar? I.e. make it accept exactly this input without modifying the input?

1 Answer 1

1

Please , read this link where is saying about the multiple rows INSERT data type.

Data Types
The values specified in a multi-row INSERT statement follow the data type conversion properties of the UNION ALL syntax. This results in the implicit conversion of unmatched types to the type of higher precedence. If the conversion is not a supported implicit conversion, an error is returned. For example, the following statement inserts an integer value and a character value into a column of type char.

CREATE TABLE dbo.t (a INT, b CHAR);  
GO  
INSERT INTO dbo.t VALUES (1,'a'), (2, 1);  
GO

When the INSERT statement is run, SQL Server tries to convert 'a' to an integer because the data type precedence indicates that an integer is of a higher type than a character. The conversion fails and an error is returned. You can avoid the error by explicitly converting values as appropriate. For example, the previous statement can be written as follows.

INSERT INTO dbo.t VALUES (1,'a'), (2, CONVERT(CHAR,1));

enter image description here

11
  • 2
    Thanks, so it's the default behaviour and it's unavoidable. Gosh, SQL Server is a real mess. Other DBs handle this with no issues.
    – Stalinko
    Jun 16, 2023 at 8:06
  • You can do this if you're looking for trouble CREATE TABLE form ( id int primary key, response sql_variant not null ); INSERT INTO form (id, response) VALUES (1, 1) INSERT INTO form (id, response) VALUES (2, 'yes'); Jun 16, 2023 at 11:53
  • @Stalinko I think most proficient developers would agree stronger data typing rules is a good thing. Helps prevent issues, such as inadvertent misaligned columns on insert etc. So quite the opposite of "a real mess". Also, each database system has minute differences around this too. Some handle it how you want, but not all do. The moral of the story is you should just write syntactically appropriate code. It also helps readability, as it's clear to someone reading the code that '1' is a string not integer based data type.
    – J.D.
    Jun 16, 2023 at 12:34
  • @J.D. actually that code is syntactically correct. That's the problem. And yes I agree that strict data typing is good, but in some edge cases it makes things so difficult. I'm writing a data connector between JSON API and DBs. For MySQL it took me couple of hours, for MSSQL couple of days fighting with all limitations.
    – Stalinko
    Jun 16, 2023 at 12:59
  • @Stalinko "syntactically appropriate" is what I said though. It causes a runtime error (which one could even argue that it's syntactically incorrect then, I just didn't go so far ;). I mean strict data typing is nothing new, and exists for good reason. I'm sure whatever actual issue you're facing has a more standardized solution though anyway. Best of luck!
    – J.D.
    Jun 16, 2023 at 13:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.