SQL Server: Import data from Excel

This article will demonstrate one of the several ways of importing data into a SQL Server table from an Excel spread sheet. I will use a simple SQL query to import data instead of SSIS/DTS. The precursor to achieving the data transfer will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server database. This can be accomplished by using the SQL Server 2005 Surface Area Configuration Utility or by executing the following code in SQL Server Management Studio’s Query Editor/Query Analyzer:

sp_configure 'show advanced options', 1
GO
reconfigure
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
reconfigure

When the server is capable of running Ad Hoc Distributed Queries we are all set to run SQL queries to import data from an MS-Excel spreadsheet. It must kept in mind that an Excel file with all the intended columns and data has to exit, before data can be imported (also the file must be closed when data is transferred). In the following code sample, data from the spreadsheet “EMP” in the Excel file, will be imported into the “employee” table in the SQL Server database using the OPENROWSET.

--Export data to Excel
select * into Employee from openrowset('Microsoft.Jet.OLEDB.4.0' ,
'Excel 8.0;Database=c:\test\test.xls','Select * from [EMP$]')

In the near future I will demonstrate how to import multiple spreadsheets into SQL Server database using SSIS, via dynamic discovery of all available spreadsheets in one Excel file.

References:
1. Server Settings Configuration Options

---

Possibly related posts: (automatically generated)

• SSIS: Export data from Sql Server 2005 to Excel
• SQL Server 2005 – Importing from a Flat File
• Copy paste data from Excel to SQL Server 2005
• have fun with SQL and Excel