Writing data back to SQL from Excel sheet

Question

I know it is possible to get data from a SQL database into an excel sheet, but i'm looking for a way to make it possible to edit the data in excel, and after editing, writing it back to the SQL database.

It appears this is not a function in excel, and google didn't come up with much usefull.

Answer 1

If you want to have the Excel file do all of the work (retrieve from DB; manipulate; update DB) then you could look at ActiveX Data Objects (ADO). You can get an overview at:

http://msdn.microsoft.com/en-us/library/ms680928(VS.85).aspx

Answer 2

You want the Import/Export wizard in SQL Management Studio. Depending on which version of SQL Server you are using, open SSMS (connect to the SQL instance you desire), right click on the database you want to import into and select Tasks.. "Import Data".

In the wizard, click Next (past the intro screen) and from the Data Source drop list select "Microsoft Excel". You specify the path and file name of the Excel spreadsheet, whether you have column headings or not.. then press Next. Just follow the wizard through, it'll set up the destination (can be SQL Server or another destination) etc.

There is help available for this process in SQL Server Books Online and more (a walkthrough) from MSDN.

If you need something deployable/more robust (or less wizard driven) then you'd need to take a look at SQL Server Integration Services (for a more "Enterprise" and security conscious approach). It's probably overkill for what you want to accomplish though.