February 19, 2009 at 3:33 am
Hi, Good day
How to export sqlserver 2005 database table to excel sheet
Eg:-My Database name is PRINCE (Sql server 2005), it having tabled called EMP
Its having 20000 records approximately, how export this EMP table data into excel sheet
Thanks & Regards
rclgoriparthi
February 19, 2009 at 7:13 am
You can use the SSIS Import Export Wizard. The following link provides information on using it.
February 19, 2009 at 7:35 am
Normally for exporting data from SQL Server to Excel one would use DTS (SQL 2k) or SSIS (SQL Server 2005). For some reason if at all you want to do it via query read on:
Step 1: Execute the code snippet
Exec sp_configure 'show advanced options', 1;
Go
Reconfigure;
Go
Exec sp_configure 'Ad Hoc Distributed Queries', 1;
Go
Reconfigure;
Go
Step 2: Create the excel file and then add the headings in the .xls file. [Important] The heading should be the same as that of the table columns.
Insert into Openrowset ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\VadivelTesting.xls;' , 'Select * from [ProductInfo$]') Select ProductID, ProductName from dbo.tblProducts
Points which might interest you:
1. As long as the file is within your C:or D: drive this sample would work. If at all your database is in a different machine from that .xls file you need to change Database=c:\VadivelTesting.xls; to UNC path. For example, Database=\\Servername\shareName (And need to provide appropriate permission).
2. Instead of "ProductInfo" replace it with your excel worksheet name.
February 19, 2009 at 12:31 pm
If this is a one time deal then you can just a SELECT * and copy/paste :hehe:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply