Write query results to Excel using a linked server and T-SQL

SQL Server has several tools for exporting and importing data. The simple utilities like the T-SQL statement BULK INSERT or the BCP utility does the data transfer by using flat text files. In order to deal with any other type of file to be exported or imported we must construct a DTS package using the DTS designer or the DTS wizard. This will eventually create a DTS package that is an independent object that we must test, maintain and handle. The work of transferring data becomes more complicated even if we want to transfer just a simple Excel file to the file system.

Here I suggest a simple but useful T-SQL stored procedure that does the export to Excel by using the linked server technology.

In order to achieve the export, you must first create an empty Excel file with a fixed name and place on the server. I called it Empty.xls and placed it in the c:temp directory. This file is not to be deleted and acts like a template for the target Excel file before it is populated by data.

The Empty.xls file is built with a single spread sheet called ExcelTable and the first (and only) line in the file consists of the letters A,B,C,...Z. These letters act like the column names of the Excel table. That means that up to 26 columns can be exported in a single query. (The given stored procedure code can be altered to support more columns in the result set. Simply write F1, F2 ,F3... in the Excel template and change the column list in the procedure to reflect the change.)

...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Management and Administration Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V SQL/Transact SQL (T-SQL) Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility SQL/Transact SQL (T-SQL) Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) Collaboration Data Objects  (SearchSQLServer.com) commit  (SearchSQLServer.com) container  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

The T-SQL stored procedure, sp_write2Excel, gets the target Excel file name and path, the number of columns in the result set and the T-SQL query. The query should use the convert function for every non-string exported column since the resulting Excel cells are actually strings.

The procedure copies the empty.xls template file to the new target Excel file. Then it builds a linked server to that file and uses dynamic T-SQL to construct and Insert/Select statements that write the data to the Excel file.

Here is the procedure code:

Example for procedure usage:

In conclusion, the procedure can be used as a general tool for exporting data to an Excel spreadsheet since the bcp utility can only export to a text file.

The usage of this procedure can substitute the usage of DTS package designer or the DTS wizard in order to export data to excel each time such an action is required.

About the author

Eli Leiba (e-mail: iecdba@hotmail.com) works at the Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies.

Rate this Tip To rate tips, you must be a member of SearchSQLServer.com. Register now to start rating these tips. Log in if you are already a member. DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.