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

excel, oledb, sql-server

Solution

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

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

Problem

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?

Original source