Conversion failed when converting the varchar to data type int when inserting multiple items

Question

I came across the following scenario:

CREATE TABLE Test (Foo NVARCHAR(MAX))
INSERT INTO Test (Foo) VALUES ('1.0')
INSERT INTO Test (Foo) VALUES (1)

Both of the inserts work fine. But when we insert them together like this:

INSERT INTO Test (Foo) VALUES
('1.0'),
(1)

I get this error

Conversion failed when converting the varchar value '1.0' to data type int

I don't understand why it tries to convert the value into an int and I can't find any documentation about this behavior. It doesn't matter what the order of the inserts is or if it's written like (N'1.0').

Answer 1

VALUES is a table value constructor, consisting of rows and columns, so data type precedence rules apply to determine the data type of the column:

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.

In your example, one value is an INT and other is VARCHAR, the winner data type is INT. When converting ALL values to INT, '1.0' fails (it is not an INT literal but a DECIMAL literal) hence the error.

For the above example, you need to perform explicit CAST on all non-varchar values i.e. CAST(1 AS VARCHAR(1)) so that the effective data type of the column is VARCHAR.