Save Excel sheet in SQL

Question

In Excel there is a panel of the "Get External Data" ribbon on the "Data" tab, where we can choose to import tables from SQL databases. This process was pleasant for me. But my question is: is there a way to store this data in SQL? Does Excel provide any API that makes it easy to code such a function indiscriminately and do it from scratch?

thanks

Answer 1

It may not be the solution you are looking for, but for a long time I returned the VBA code that would take the range in Excel and convert it to XML and create SQL to put this data in the temp table in SQL Server. Here is a link if you are interested.

Answer 2

The easiest way to do this is to use the import function in SSMS. You can choose which sheets to use, configure column mappings, etc. If SSIS is created, you can then manipulate further if necessary. However, this approach is a traction from Sql, not a click on Excel, if you want to do this, you will have to code some VBA to do it for you.

Answer 3

Non-software:

http://office.microsoft.com/en-us/excel-help/connect-to-import-sql-server-data-HA010217956.aspx

Programmatically - I can only think of the OpenRowSet function in MSSQL:

http://www.mssqltips.com/tip.asp?tip=1540

http://www.sql-server-helper.com/tips/read-import-excel-file-p01.aspx

Using openrowset to read an Excel file into a temporary table; How can I refer to this table?