SQL Server: Data transfer for Excel using Linked Server

Introduction

I will demonstrate yet another way to perform data transfer to and from an excel file using a Linked Server in SQL Server 2008. In this demo I will show you how to:

• Create a Linked Server using Excel as the data server
• Import data to a SQL Server table from a spreadsheet
• Export data to a spreadsheet from a SQL Server table

Create a Data Source

As the first step in this process I have created an Excel file with a spreadsheet named “Employee”, followed by defining the headers and inserting 5 records as showing in following illustration:

Create a Linked Server

Now that we have a data source let us create a linked server using an the Excel file created in the previous step as the data source. Open SQL Server Management Studion (SSMS) and expand “Server Objects” under the intended SQL Server, to find the Linked Servers item.Right click on Linked Servers and click on “New Linked Server” to see following dialog box. EXCEL_LINKED_SERVER is the name that I have chosen to call this linked server. Then we need to populate the Provider, Product Name, Data Source and Provider String like in this example and click the OK button.

A linked server should have been created successfully at this time. Right click on the “Linked Server” tree view item and click on refresh to see the newly created linked server. Expand the linked server to see the Exployee$ spreadsheet under the list of tables.

Import Data

At this point you should be able to access the Excel spread sheet just like any other SQL Server database table.  Let us import data into a table named DataFeed in the Demo database. It is important to note that the spreadsheet name in the SQL query has to be accessed only by preceding spreadsheet name  with “…”  Only one dot is used to represent the current database, two dots to represent another database in the SQL Server instance and three dots represent a Linked Server. When we would have ran the following SQL query, a table named “DataFeed” would have been created and 5 records from this table would be displayed in the results pane.

Export Data

Let us insert a couple of records into the DataFeed table that was created in the above step, followed by exporting those two rows to the spreadsheet. When the following query us run, you will observe that the spread sheet will have 7 records.

Now Let us look at the new data in the spreadsheet:

SQL Server: Export data to Excel

This article will demonstrate one of the several ways of exporting data from SQL Server 2005 to an Excel spread sheet. This time we will use simple SQL queries to export data unlike the earlier instance when we used SSIS. The precursor to achieving this will be to perform a onetime operation via, enabling the running of Ad Hoc Distributed Queries on your SQL Server. 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:

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 export data to Excel spreadsheet. It must kept in mind that an Excel template with all the intended columns has to exit before data can be exported and the file must be closed while data is transferred. In the following example data from the “employee” table in the SQL Server 2005 database is being exported to an Excel spreadsheet named “EMP”.

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

References:
1. Server Settings Configuration Options

SSIS: Export data from Sql Server 2005 to Excel

Exporting data from a SQL Server 2005 to MS-Excel using SQL Server Integration Services (SSIS) is one of the simple ways of data transfer. This article demonstrates a step-by-step procedure of exporting data from Microsoft SQL Server to Microsoft Excel worksheets.

Update: 06/22/09 – You can also also check out my blog on Exporting data to Excel with out using SSIS now.

1. Open Visual Studio 2005 IDE and create a new Integration Services Project.

2. Add a Data Flow Task item to the Control Flow pane and double click on it.

3. Add a OLE DB Source item to the Data Flow pane and configure it to your Source Sql Server 2005 database.

4. Add an Excel Destination item to the Data Flow pane.

5. Create a connection between the two items by dragging the green arrow on the source item and dropping it on the destination item.

6. Configure the Excel Destination Item so that it points to the destination Excel file, then make sure that the mappings between the source table and the destination table are correct.

7. Compile and execute the package to export the data.

Note: This kind of exporting will work only when you have a pre-formated (with data columns) spread-sheet in the Excel file. Make sure that the Excel file in this context is always closed while exporting data.

Following are some other ways of exporting to an Excel file:

• Use Export wizard from Sql Server 2005.
• Use distributed queries [1]
• Write a C#/VB.Net program. [2]
• etc

References:
1. SQL SERVER – 2005 – Export Data From SQL Server 2005 to Microsoft Excel Datasheet. – Pinal Dave
2. Data Transfer from SQL Server to Excel – Levent Camlibel
3. MSDN Online