I don't know how to have your update or insert query read an excel
> spreadsheet, maybe someone more experienced with SQL will reply........
How to link sql server to an excel spreadsheet
- Create a folder that SQL server has rights to access, EG: C:\Data \Import\
- Place your spreadsheet in the folder EG: ConvolutedSomething.xls with a worksheet called MySheet
- Create a linked server to tell sql server about your folder
3.a You can do this with a script but I prefer to do it interactively
3.b If you use a script the tricky bits are SQL server rights to access the folder and setting the password
3.c Download, install and open (free) Microsoft sql server management studio express
3.d Connect and go to server objects>linked servers and make a new linked server
3.e Enter and OK the following Name: MyLinkedXLFile Server type: Other Provider: Microsoft Jet 4.0 OLE DB Provider Product name: Excel Data source: C:\Data\Import\ConvolutedSomething.xls Provider string: Excel 8.0
- Run a query EG: select * from MyLinkedXLFile...MySheet$ (you might need to restart sql server first)
- You can do far more with linked servers
5.a Such as link to CSV and tab delimited files in that folder by putting a schema.ini file in that folder which tells sql server how to read your files that are not spreadsheets.
5.b To find out more, search on google for "microsoft schema.ini" and look at the result for Microsoft MSDN end