Using SQL Query to Automatically Send Data to Excel

• Otis-844278

  Valued Member

  Points: 66

  More actions

  April 3, 2009 at 9:31 am

  #384283

  Hello.

  I have what I hope is a simple question. It probably has been asked may times.

  I would like to place in my SQL Query (running using SQL Server Management Studio [2008]) the JCL which will permit the query to automatically send the results to an Excel spreadsheet.

  Usually the query results in ~8000+ rows and produces the required results. I can indicate that I want the results to be sent to 'File' (*.rpt) and then open Excel and import the results; but, this is a time consumming task. Plus, I share my queries with people who even more novice than I :w00t: and would like them to not have to go through the file format import routine.

  (As information, the query JOINS 11 tables so it is not a simple task of just sending one table to Excel.)

  So, back to my question, is there a bit of code which can be included in the query which will send the results to an Excel spreadsheet?

  I thank you, in advance, for any guidance.

• arun.sas

  SSChampion

  Points: 11831

  More actions

  April 6, 2009 at 1:00 am

  #972312

  Hi,

  In sql 2000

  Try this in your sp

  insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

  'Excel 8.0;DatabaseC:\TEST.XLS',

  'SELECT * FROM [Sheet1$]')

  Select COL1,

  COL2

  from Table (Nolock)

  whereCONDITIONS

  Ensure that in that drive(C:\) the excel file available with name TEST and all column names(COL1,COL2)

  Regards,

  ARUN SAS