Systems Engineering and RDBMS

Archive for January 24th, 2008

Extracting data into Excel or text file from SQL Server

Posted by decipherinfosys on January 24, 2008

There are many ways of extracting the data from a SQL Server table to Excel or a text file. Here are the options that you can chose from:

  1. Using DTS (SQL Server 2000) or SSIS (SQL Server 2005) – either through the Export/Import Wizard or through a custom package.
  2. BCP: Using BCP, you can extract the data out and dump it to an excel spreadsheet or a text file.
  3. Custom code written using SQL-DMO (SQL Server Distributed Management Objects) in SQL Server 2000 or SQL-SMO (SQL Server Management Objects) in SQL Server 2005.
  4. Directly through SQL Server Management Studio (Save Results AS).
  5. T-SQL:

a) Using a Linked Server.
b) Using the OPENROWSET() function.

In this post, we will take a look at #5 from above. Let’s create a linked server first (and it’s login information):

EXEC sp_addlinkedserver EXCEL_LS,
‘Jet 4.0’,
‘Excel 8.0’

EXEC sp_addlinkedsrvlogin
EXCEL_LS, /** remote server name **/
false, /** True or False **/
NULL, /** local login Name **/
NULL, /** Remote userName **/
NULL /** Remote password **/

This creates the linked server and the login. The caveat to this is that the excel spreadsheet Login_Data.xls should already be present at the drive that is listed and should already have the columns in it that you intend to insert into it. So, in this case, we created the Login_Data.xls spreadsheet with two columns in it on the first tab (Sheet1): LoginName and CreateDate (with the right format). And now, let’s insert the data into it using the two options mentioned in point #5:

a) Using the linked server:

insert into EXCEL_LS…[Sheet1$] SELECT loginname, createdate from master.dbo.syslogins

(16 row(s) affected)

b) Using the OPENROWSET() function:

INSERT INTO OPENROWSET(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0; Database=C:\Login_Data.xls;’, ‘SELECT loginname, createdate FROM [Sheet1$]’)
SELECT loginname, createdate from master.dbo.syslogins

(16 row(s) affected)

So, this is yet another way to extract the data. My personal preference is still bcp and #3 (Custom Code using SQL_DMO and SQL-SMO) but it is always good to know the different options that one has.

Posted in SQL Server | Leave a Comment »