Set Excel File As A Database in MS-SQL

1. Requirements

- Microsoft Access Database Engine 2010

(If you are on the Windows 64bit, you need both Microsoft Access Database 64bit and Microsoft Office 64 bit)

Download - http://www.microsoft.com/download/en/details.aspx?id=13255

2. Execute Stored Procedure

USE msdb
GO
EXEC sp_addlinkedserver
 @server = 'EXCEL_DB',                              
 @srvproduct = 'ACE 12.0',
 @provider = 'Microsoft.ACE.OLEDB.12.0',
 @datasrc = 'C:\FileName.xls',                // full path of excel file
 @provstr = 'Excel 12.0; HDR=Yes'         // HDR=yes, when excel file has header row

Table name is set by sheet name of excel file.

3. How To Access

- SELECT

SELECT * FROM EXCEL_DB...[Customers$]

- IMPORT

SELECT * INTO XLImport1 FROM EXCEL_DB...[Customers$]