Importing an Excel Sheet to a Database Table.

I've always found the SQL Server Import and Export Wizard a great way to pull data out of Excel into SQL Server. Except for when it doesn't work. It can be a frustrating beast, with all kinds of little idiosyncratic things to consider.

However, it's easily avoided for basic imports from Excel. If you just want a table in a SQL database which looks and feels exactly like the source data, this command is your new friend:

SELECT
         *
         INTO
         dbName.schemaName.tableName
   FROM
         OPENROWSET('Microsoft.ACE.OLEDB.12.0'
            , 'Excel 12.0;Database=excelFilePathName.xlsx'
            , [worksheetName$])

Works a charm, and avoids clicking through a GUI which tends to crash when there's any slight problems.

If you have any "missing provider" problems, which may happen on new 64 bit SQL servers, download the provider from Microsoft Access Database Engine 2010 Redistributable.