how to copy the result data from stored procedure to excel

Question

pls can anyone write query it soo.. help me on this

Jothish Kumar · Answer

Hi just try this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104935[^]

Gautam Parmar · Answer

Hi Try below code.. 1 Export data to existing EXCEL file from SQL Server table insert into OPENROWSET( 'Microsoft.Jet.OLEDB.4.0'  'Excel 8.0;Database=D:	esting.xls;'  'SELECT * FROM [SheetName$]' ) select * from SQLServerTable 2 Export data from Excel to new SQL Server table select * into SQLServerTable FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0'  'Excel 8.0;Database=D:	esting.xls;HDR=YES'  'SELECT * FROM [Sheet1$]' ) 3 Export data from Excel to existing SQL Server table Insert into SQLServerTable Select * FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0'  'Excel 8.0;Database=D:	esting.xls;HDR=YES'  'SELECT * FROM [SheetName$]' ) 4 If you dont want to create an EXCEL file in advance and want to export data to it use EXEC sp_makewebtask @outputfile = 'd:	esting.xls'  @query = 'Select * from Database_name..SQLServerTable'  @colheaders = 1 @FixedFont = 0@lastupdated = 0@resultstitle = 'Testing details' (Now you can find the file with data in tabular format) 5 To export data to new EXCEL file with heading( column names) create the following procedure create procedure proc_generate_excel_with_columns ( @db_name varchar (100) @table_name varchar (100) @file_name varchar (100) ) as --Generate column names as a recordset declare @columns varchar (8000) @sql varchar (8000) @data_file varchar (100) select @columns = coalesce (@columns + ''  '' ) + column_name + ' as ' + column_name from information_schema.columns where table_name = @table_name select @columns = '' '' '' + replace ( replace (@columns ' as '  '' '' ' as ' ) ''  '' '' '' ) --Create a dummy file to have actual data select @data_file = substring (@file_name 1 len(@file_name) - charindex( '\'reverse(@file_name)))+' \data_file.xls' --Generate column names in the passed EXCEL file set @sql = 'exec master..xp_cmdshell ' 'bcp  select * from (select ' + @columns + ') as t queryout ' + @file_name + ' -c' '' exec (@sql) --Generate data in the dummy file set @sql = 'exec master..xp_cmdshell ' 'bcp select * from ' + @db_name + '..' + @table_name + ' queryout ' + @data_file + ' -c' '' exec (@sql) --Copy dummy file to passed EXCEL file set @sql = 'exec master..xp_cmdshell ' 'type ' + @data_file + ' >> ' + @file_name + '' '' exec (@sql) --Delete dummy file set @sql = 'exec master..xp_cmdshell ' 'del ' + @data_file + '' '' exec (@sql) After creating the procedure  execute it by supplying database name  table name and file path EXEC proc_generate_excel_with_columns 'your dbname'  'your table name'  'your file path' Thanks