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!
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