How to import data from an Excel File using T-SQL

If you have an Excel File named ExcelFile.xls, then you can perform SELECTs on that Excel file using the Excel Sheet name and the Jet Provider.

FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Data Source=C:\ExcelFile.xls;Extended Properties="Excel 8.0"')...[Data_Load$];

In case, you use the same Excel file over and over again, then you can use OPENQUERY using a Linked Server defined to your Excel File.

FROM OPENQUERY (DataLoad, 'select * from [Data_Load$]')

If you find that the data being imported for non-Text columns is NULL, then refer the following blog entry: