INSERT INTO fails due to incorrect conversion T-SQL

Question

My table looks like this:

CREATE TABLE MyTable
(
    TableID INT IDENTITY NOT NULL,
    ForeignID INT NOT NULL,
    Value sql_variant NOT NULL,
    CodeOne VARCHAR(4) NOT NULL,
    CodeTwo VARCHAR(4) NOT NULL
)

I'm trying to do a insert with the following code:

    INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo)
    VALUES
    (
        1,
        'FooBar',
        'Foo',
        'Bar'
    )

I get the following error in SQL Server Mgmt studio when I try to execute the code above:

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

The only reason why it might be trying to convert 'FooBar' to int if it thinks 1 is the primary key and 'FooBar' is in the position of ForeignID. My understanding of INSERT INTO...VALUES is that you don't need to provide the primary key since it should be auto generated. Please correct me if I'm wrong.

I'm using this page as a reference: http://www.databasejournal.com/features/mssql/tsql-understanding-how-to-use-the-insert-statement-to-populate-your-database-tables.html

Any ideas on why 'FooBar' is being converted to an int? Thanks!

More Info:

I'm using SQL Server 2012

Version Info: Microsoft SQL Server Management Studio 11.0.2100.60 Microsoft Analysis Services Client Tools 11.0.2100.60 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 9.11.9600.16384 Microsoft .NET Framework 4.0.30319.18444 Operating System 6.1.7601

Your column _Value _ have type _sql_variant_ and i think this is simple integer, and you try to put there varchar type — The Reason, Jun 22 '15 at 12:02
Your INSERT statement is OK. Is there perhaps a trigger on the table? Code that specifies an integer literal against the sql_variant column will cause this error if you have character data in the column due to implicit conversion. — Dan Guzman, Jun 22 '15 at 12:03
The insert statement does look good and should not lead to a conversion of FooBar to int. Try using [] around the names of the columns (like INTO MyTable([ForeignID], [Value], [CodeOne], [CodeTwo]) This prevents SQL from confusing commands and columnnames. Also the use of sql_variant may not be the best choice. — Tom, Jun 22 '15 at 12:04
@SpartaSixZero: You need to add IDENTITY to your primary key column. Check this link: http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 — Paresh J, Jun 22 '15 at 12:12
The reason we used sql_variant as the type was because Value was designed to store varchars, ints, and bits. — SpartaSixZero, Jun 22 '15 at 12:13
Please do not change the important information regarding question after somebody answer it . Please keep in mind this , if you can include all the information when you are asking , in this case your first table structure is different from the structure after edit. Before edit TableID was not identity column, if you editing something in question then atleast mark it as an update — Arunprasanth K V, Jun 22 '15 at 12:29
also one more thing your query is working perfectly in sql server 2008 r2 version with out any casting , which sql version you are using ?? — Arunprasanth K V, Jun 22 '15 at 12:31
@ArunprasanthKV Sorry about that. I should have included everything at the beginning. I understand people's time is precious. I updated sql version in my question. — SpartaSixZero, Jun 22 '15 at 12:35
when you updating something in question just mention as update. — Arunprasanth K V, Jun 22 '15 at 12:37

Luaan · Accepted Answer · 2015-06-22T12:23:28.470

2

The problem is that there's no implicit conversion from varchar (your literal) to sql_variant. Just add an explicit conversion and you're done:

cast('FooBar' as sql_variant)

edited Jun 22 '15 at 12:23

answered Jun 22 '15 at 12:17

Luaan

62,244
7
97
116

@SpartaSixZero, which version SQL Server you are trying to cause it should work just fine in 2008 R2 and 2012 without any casting. – Rahul Jun 22 '15 at 12:26
@SpartaSixZero, strange!!! cause I just tried in 2012 and it worked just fine. Anyways, Luaan given a nice answer. – Rahul Jun 22 '15 at 12:27
Very strange! Here is my version info: Microsoft SQL Server Management Studio 11.0.2100.60 Microsoft Analysis Services Client Tools 11.0.2100.60 Microsoft Data Access Components (MDAC) 6.1.7601.17514 Microsoft MSXML 3.0 4.0 5.0 6.0 Microsoft Internet Explorer 9.11.9600.16384 Microsoft .NET Framework 4.0.30319.18444 Operating System 6.1.7601 – SpartaSixZero Jun 22 '15 at 12:29
@Rahul It shouldn't help for such a simple `insert` - that should be handled by the implicit conversion just fine. The problem starts when you're using `insert ... select` - the conversion will actually happen long before it gets to the `insert`. Try `insert into @table ([Test]) select 3 union all select 'FooBar'` for example - the unioned selects must have a proper type for their "columns", and the `int` value takes precedence in that. Using `sql_variant` explicitly takes care of that. – Luaan Jun 22 '15 at 12:39
@Luaan, where does `insert into ... select from` coming into picture here. isn't OP doing a direct INSERT query? at least that's what I see from post. – Rahul Jun 22 '15 at 12:42
@Rahul Well, honestly, I've never used `sql_variant` without doing the explicit conversion. There's just too many cases where it doesn't work as I'd like :D The OPs code actually works fine for me on 2008 R2 as well. It might have something to do with the data already in the table, or the query execution planner... the more dynamic you get, the more useful it is to just close your eyes and think of England :D – Luaan Jun 22 '15 at 12:48

score 1 · Answer 2 · answered Jun 22 '15 at 12:12

I do not think this is because of primary key constraint on the column. And one important thing to be noted here is, primary cannot be an identity column by default. The reason for failure might be because of sql_variant declaration for value column. It cannot support varchar data types. https://msdn.microsoft.com/en-IN/library/ms173829.aspx . Please try to make the primary column as identity column and Value column as varchar or nvarchar column (based on the scenario) and try insert statement again.

Actually, it doesn't support `varchar(max)`. It does support e.g. `varchar(6)` just fine. — Luaan, Jun 22 '15 at 12:23

INSERT INTO fails due to incorrect conversion T-SQL

More Info:

2 Answers2