Export data from SQL Server2000 to Excel besides BCP

demo

Apart from using DTS and Export wizard, we can also use this query to export data from SQL Server2000 to Excel

Create an Excel file named testing having the headers same as that of table columns and use these queries

1 Export data to existing EXCEL file from SQL Server table

1. insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   
2.     'Excel 8.0;Database=D:\testing.xls;',
   
3.     'SELECT * FROM [SheetName$]') select * from SQLServerTable
   
4. 
   

复制代码

2 Export data from Excel to new SQL Server table

1. select *
   
2. into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   
3.     'Excel 8.0;Database=D:\testing.xls;HDR=YES',
   
4.     'SELECT * FROM [Sheet1$]')
   
5. 
   

复制代码

3 Export data from Excel to existing SQL Server table

1. Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
   
2.     'Excel 8.0;Database=D:\testing.xls;HDR=YES',
   
3.     'SELECT * FROM [SheetName$]')
   
4. 
   

复制代码

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

1. EXEC sp_makewebtask
   
2.         @outputfile = 'd:\testing.xls',
   
3.         @query = 'Select * from Database_name..SQLServerTable',
   
4.         @colheaders =1,
   
5.         @FixedFont=0,@lastupdated=0,@resultstitle='Testing details'
   
6. 
   

复制代码

(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

1. create procedure proc_generate_excel_with_columns
   
2. (
   
3.         @db_name        varchar(100),
   
4.         @table_name        varchar(100),        
   
5.         @file_name        varchar(100)
   
6. )
   
7. as
   
8. 
   
9. --Generate column names as a recordset
   
10. declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)
    
11. select
    
12.         @columns=coalesce(@columns+',','')+column_name+' as '+column_name
    
13. from
    
14.         information_schema.columns
    
15. where
    
16.         table_name=@table_name
    
17. select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')
    
18. 
    
19. --Create a dummy file to have actual data
    
20. select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'
    
21. 
    
22. --Generate column names in the passed EXCEL file
    
23. set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''
    
24. exec(@sql)
    
25. 
    
26. --Generate data in the dummy file
    
27. set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''
    
28. exec(@sql)
    
29. 
    
30. --Copy dummy file to passed EXCEL file
    
31. set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''
    
32. exec(@sql)
    
33. 
    
34. --Delete dummy file
    
35. set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''
    
36. exec(@sql)
    
37. 
    

复制代码

After creating the procedure, execute it by supplying database name, table name and file path

1. EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

复制代码

Madhivanan
http://www.sqlteam.com/forums/topic.asp?topic_id=49926