Inserting xls file from query

[sparkme]

Is there any script in sql to insert excel file
like to insert the output of the following query
select * from authors

I know I can do this from DTS but I want to do from sql query.

Thanks

 

[hmckillop]

Sorry not clear.
Do you want to insert into an excel file from a SQL db
or from a SQL db to an excel file.

If its the later, and its a one off, run the query in QA and have the save results in text file. this allows you to save as a csv file which will open in Excel.
Alternatively use bcp and have an output file.

If its the first option and you have data in excel, some vba code would be best option, and then use ADO to connect to you db.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[plevey]

Ypu could try something lke this...

declare @WsheetNameVarChar(100)
declare @WsheetPath VarChar(100)
declare @SQLCommand VarChar(600)

 -- set path to worksheets

set @WsheetPath = 'C:\Test\'
set @WsheetName = 'TestSheet.xls'

-- Create a linked server connection to the worksheet. 

      SET    @SQLCommand = 
            'EXEC master..sp_addlinkedserver '+ 
                        '''' + 'Excel'  + '''' + ',' + 
                        '''' + 'Jet 4.0' + '''' + ',' + 
                        '''' +     'Microsoft.Jet.OLEDB.4.0' + 
                        '''' + ',' + '''' + @Wsheetpath + 
                        @WsheetName + '''' + ',' + 
                        'NULL,' + '''' + 'Excel 8.0' + ''''

     EXEC (@SQLCommand)

-- login to the linked server

    EXEC sp_addlinkedsrvlogin 'Excel', FALSE

-- Now that the worksheet is "attached" insert data from table somefile -

INSERT into Excel...Data select * fron somefile

-- Cleanup

  EXEC sp_droplinkedsrvlogin 'Excel',NULL
  EXEC sp_dropserver 'Excel', NULL

If fishing was easy it would be called catching