Excel and SQL Server Integration using OLEDB
Configure the Microsoft.ACE.OLEDB.12.0 to insert or update data in an Excel spreadsheet from SQL Server by using the OPENROWSET command in Transact-SQL to provide an elegant and easy to use interface. There are a couple of easy steps to perform to be able to use this OLEDb interface technology which are provided below.
First you have to set a couple Registry parameters which can be managed with these two commands:
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
You also have to enable the Distributed Queries in the Surface Area Configuration using this set of commands
SP_CONFIGURE 'show advanced options', 1
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
Of course you also have to tell the data provider that you are dealing with an Excel file and not an Access database which is the default using the Extended Properties clause:
Select ProductCategory, ProductName,sum(case when TransactionType = ‘Sales’ then Amount else 0 end) as Sales,sum(case when TransactionType = ‘Budget’ then Amount else 0 end) as Budgetfrom OPENROWSET(‘Microsoft.ACE.OLEDB.12.0′,‘Excel 12.0;Database=C:\GizmoSales.xlsx;Extended Properties=”Excel 12.0 Xml;HDR=YES”;’, ‘Select * from [SalesData$]‘)group by ProductCategory, ProductName
When referencing the Excel sheet T-SQL you have to use this syntax if not you will get an error stating that the sheet doesn't exist:
If In Excel the sheet name is : Temp (as an example)
then in T-SQL you need to reference it as [Temp$]
Other than that you can use openquery, openrowset or opendatasource as you would in previous versions of Excel and SQL Server.