Answered by:
How to export data from sql server to excel file through queries?

Question
-
I want to export the records in sql server file through sql server queries
How can i do it?
Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.comMonday, June 28, 2010 4:48 AM
Answers
-
Hi.
Here is a c# code:
SqlConnection con; SqlCommand cmd; SqlDataAdapter da; DataSet ds; con = new SqlConnection("YourConnectionString"); cmd = new SqlCommand("SELECT * FROM Your_Table", con); con.Open(); da = new SqlDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); ds.WriteXml(@"c:/File_Name.xls");
Noam B.
Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...Monday, June 28, 2010 11:13 AM -
Hi,
You can try to use OPENROWSET T-SQL statement Export the output of SELECT statement to Excel. Try the following.
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\YourExcelFile.xls;', 'SELECT * FROM [SheetName$]') SELECT * FROM yourTable
Refer the following links.
- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 5:26 AM -
I want to export the records in sql server file through sql server queries
How can i do it?
Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.com
Y don't u try simple select and insert commandSelect for SQL and Insert Command for Excel
Regards Kumar Gaurav. Please Mark as an answer in case the post is helpful to you so that it became helpful to others- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 5:45 AM -
Hi Pranil,
Do you want to export the records from SQL Server using T-SQL and SELECT statements? If so, you can try to use the method posted in my previous reply. Also you can try post this question in SQL Server Forums .
Also please let me know if you want to export the content from SQL Server into Excel using C#.
- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 12:26 PM -
Everyone here has given great answeres, I just wanted to add that if this is a live DB or serving many users while you export, be sure to add WITH NOLOCK in your select *.
- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 5:49 PM
All replies
-
Hi,
You can try to use OPENROWSET T-SQL statement Export the output of SELECT statement to Excel. Try the following.
INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\YourExcelFile.xls;', 'SELECT * FROM [SheetName$]') SELECT * FROM yourTable
Refer the following links.
- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 5:26 AM -
I want to export the records in sql server file through sql server queries
How can i do it?
Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.com
Y don't u try simple select and insert commandSelect for SQL and Insert Command for Excel
Regards Kumar Gaurav. Please Mark as an answer in case the post is helpful to you so that it became helpful to others- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 5:45 AM -
Hi.
Here is a c# code:
SqlConnection con; SqlCommand cmd; SqlDataAdapter da; DataSet ds; con = new SqlConnection("YourConnectionString"); cmd = new SqlCommand("SELECT * FROM Your_Table", con); con.Open(); da = new SqlDataAdapter(cmd); ds = new DataSet(); da.Fill(ds); ds.WriteXml(@"c:/File_Name.xls");
Noam B.
Do not Forget to Vote as Answer/Helpful, please. It encourages us to help you...Monday, June 28, 2010 11:13 AM -
Hi Pranil,
Do you want to export the records from SQL Server using T-SQL and SELECT statements? If so, you can try to use the method posted in my previous reply. Also you can try post this question in SQL Server Forums .
Also please let me know if you want to export the content from SQL Server into Excel using C#.
- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 12:26 PM -
Everyone here has given great answeres, I just wanted to add that if this is a live DB or serving many users while you export, be sure to add WITH NOLOCK in your select *.
- Marked as answer by Harry Zhu Monday, July 5, 2010 9:34 AM
Monday, June 28, 2010 5:49 PM -
this solution is working correctly but for more than 10 lakhs records it will take more time
Thanks and Regards, Pranil Yambal Pranil.Yambal@hotmaail.comFriday, July 23, 2010 9:37 AM