How to import data from Excel to SQL Server Table?

Use Distributed Queries

If you do not want to configure a persistent connection to the Excel workbook as a linked server, you can import data for a specific purpose by using the OPENDATASOURCE or the OPENROWSET function. The following code samples also import the data from the Excel Customers worksheet into new SQL Server tables:

SELECT * INTO LIT FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',                                                                               'Excel 8.0;Database=C:\test\xltest.xls',  'SELECT * FROM [Sheet1$]')

Troubleshooting

Remember that Excel object names that are appended with a dollar sign ($) represent worksheets (for example, Sheet1$) and that plain object names represent Excel named ranges.
In some circumstances, especially when you designate the Excel source data by using the table name instead of a SELECT query, the columns in the destination SQL Server table are rearranged in alphabetical order.