1

I have a strange behavior when inserting multiple rows in a table with mixed types in the values.

The SQL for creating the table is:

CREATE TABLE [dbo].[insert_test]
(
    [col1] [nchar](10) NULL
) ON [PRIMARY]

When inserting multiple values (with mixed types) I get an error:

INSERT INTO insert_test (col1) 
VALUES ('test'), (1);

Error:

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

I do not see, why the server is trying to convert 'test' to an int.

I would expect, that it uses the type of the column I'm using (in this case nchar from column col1).

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
gerhardgruber
  • 31
  • 4
  • 2
    Did you try `values ('test'), ('1');` which would be the correct data types – juergen d Sep 04 '17 at 11:26
  • Yes, I tried and in this way it works. The reason why the insert is done in the way I mentioned is a bug in a Rails app. But I do not see why the SQL server behaves this way... – gerhardgruber Sep 04 '17 at 11:27
  • 1
    Then SQL Server tries to unify the values before inserting. – juergen d Sep 04 '17 at 11:27
  • Ok. But why? And is there a way to avoid this? I mean the type of the values should be derived from the column, shouldn't it? – gerhardgruber Sep 04 '17 at 11:28
  • 1
    Why don't you just provide the correct data types in the first place? `1` is an `int` and obviously not a `nchar` – juergen d Sep 04 '17 at 11:30
  • A column must be a single type so SQL Server will convert the literal values in the row constructor to the type with the highest precedence (int here). The attempt to convert 'test' to integer will then fail. The type is derived from the source, not the target. – Dan Guzman Sep 04 '17 at 11:30
  • I totally agree, that it would be better to use the correct data types. And we fixed the bug in our application, to do so. But I tried to understand why I'm getting the error from the database... – gerhardgruber Sep 04 '17 at 11:31
  • Perhaps the bigger question is why you think you need a column to store two logically different types. The best answer might be to find a workaround such that you don't even need to ask this question. – Tim Biegeleisen Sep 04 '17 at 11:31
  • @DanGuzman but wouldn't it be better to take the type from the column of the table I'm trying to insert here? – gerhardgruber Sep 04 '17 at 11:32
  • @gerhardgruber: Why doin't you then conntact the SQL server team and discuss it with them? – juergen d Sep 04 '17 at 11:32
  • @TimBiegeleisen in the real table we are storing article numbers of various products which may just be digits or other characters. Therefore we chose the nvarchar type, but the application had a bug, where it converted just-digit-strings to an integer. – gerhardgruber Sep 04 '17 at 11:33
  • @juergend I think I will do this, but I just gave a try to ask here, because maybe someone else had the same situation until now. – gerhardgruber Sep 04 '17 at 11:33
  • @gerhardgruber, I think the Micosoft SQL Server product team has given this more thought than you or I but I would guess the source rowset has to be constructed first, before the final conversion to the target type is applied. – Dan Guzman Sep 04 '17 at 11:34

1 Answers1

4

According to the Table Value Constructor documentation:

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.

There is no mention of the target table type because constructors can be used in contexts other than just INSERT statements. I think the documented behavior provides consistency among different use cases.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71