SQL Server, "Invalid column name" error when inserting into Excel "linked server"

Emmanuel picture Emmanuel · Mar 27, 2014 · Viewed 25k times · Source

I have a simple Excel spreadsheet document (running Office 2013) that I'm using as a database using the "Microsoft Office 15.0 Access Database Engine OLE DB Provider".

I can connect to this using the MS SQL Server Management Studio 2012, and I can even select data from a Named Range "Employee".

SELECT *
  FROM [X]...[Employee]
GO

The result:

ID  Name    Description Salary
1   Rob     Cool        1
2   Bob     Awesome     2
3   Robert  DUDE!       3

Now I'd like to insert data here. So I wrote:

INSERT INTO [X]...[Employee]
           ([ID]
           ,[Name]
           ,[Description]
           ,[Salary])
     VALUES
           (4
           ,"John"
           ,"Boss"
           ,500)

This is actually mostly generated by SQL Server Management Studio. When I run this, I get:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'John'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Boss'.

Any idea what I'm doing bad?

Answer

rory.ap picture rory.ap · Mar 27, 2014

You're using double quotes instead of single quotes. IE:

INSERT INTO [X]...[Employee]
       ([ID]
       ,[Name]
       ,[Description]
       ,[Salary])
 VALUES
       (4
       ,'John'
       ,'Boss'
       ,500)