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 [e-mail]

Answer 1

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.

SqlServer to Excel export with OPENROWSET

Export data from SQL Server to Excel

Answer 2

I want to export the records in sql server file through sql server queries

How can i do it?

---

Thanks and Regards, Pranil Yambal [e-mail]

Y don't u try simple select and insert command

Select 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

Answer 3

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...

Answer 4

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#.

 

 

Answer 5

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 *.

Answer 6

this solution is working correctly but for more than 10 lakhs records it will take more time

---

Thanks and Regards, Pranil Yambal [e-mail]