Updating access database from excel
Although this process works are you familiar with any other options to directly perform the INSERT, UPDATE or DELETE operations?
Are their any benefits to transitioning our code to another approach with the OPENROWSET command?
A second consideration with SQL Server 2005 is that the Surface Area Configuration setting must be enabled to use this code in either case.
Finally, with either approach be sure to clean up any Excel spreadsheets or temporary tables once you are finished with them.
Yes - You are correct the OPENROWSET command can directly support INSERT, UPDATE or DELETE operations as shown in these tips: Export data from SQL Server to Excel and Different Options for Importing Data into SQL Server.
We always upload the data to a table and then begin the process.
Because Employee ID is the only key in these two Table, I drag the Employee ID field of one Table to Employee ID field of the other Table in order to create an Inner Join.
The result is immediately displayed in the lower table.
Let's work through some examples with the SQL Server 2005 Adventure Works sample database for each operation with a sample Excel spreadsheet.
Prerequisites For all of these examples, please do the following: General Analysis In terms of transitioning your code to the new approach, I have yet to experience any performance issues with 1000's of records, but that seems to be the first concern.The main command used in one of the tips is OPENROWSET.