Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to export sqlserver 2005 database table to excel sheet Expand / Collapse
Author
Message
Posted Thursday, February 19, 2009 3:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 19, 2009 3:31 AM
Points: 10, Visits: 26
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
Post #660148
Posted Thursday, February 19, 2009 7:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 22, 2010 12:45 PM
Points: 16, Visits: 103
You can use the SSIS Import Export Wizard. The following link provides information on using it.

http://www.databasejournal.com/features/mssql/article.php/3580216/SQL-Server-2005-Import--Export-Wizard.htm
Post #660275
Posted Thursday, February 19, 2009 7:35 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 6:00 AM
Points: 806, Visits: 413
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.
Post #660307
Posted Thursday, February 19, 2009 12:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, May 23, 2013 12:42 PM
Points: 29, Visits: 93
If this is a one time deal then you can just a SELECT * and copy/paste
Post #660723
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse