How to Export Data from SQL Server to Excel Automatically
"Hello,I am new to SQL.We use SQLquery in SQL2016Server. And then we are exporting the output to excel.Could you please guide me on how to make the process automated for exporting data from SQL to Excel. SinceIdon't want todo the task manuallyany longer. "
If you come across the same problem as mentioned above and want to find a way to export SQL Server data to Excel, don't fret. This page covers two possible ways to help you export data from SQL Server automatically.
Two Ways to Export Data from SQL Server to Excel
You can use the SQL Server Import and Export Wizard andData Connection Wizard in Excel to export data from SQL Server to Excel. See the details below.
Solution 1. Use the SQL Server Import and Export Wizard
1.Open SQL Server Management Studio and connect to the database.
2. Go to "Object Explorer", find theserver database youwant to export to Excel. Right-click on itand choose"Tasks" >"Export Data"to export table data in SQL.Then, the SQL Server Import and Export Wizard welcome window pop up.
3. Now, you need to click on the Data source drop-down button to choose the data source you want to copy. Here, select "SQL Server Native Client 11.0". In the Server name drop-down box, select a SQL Server instance. In the Authentication section, choose authentication for the data source connection and from the Database drop-down box, select a database from which data will be copied. After everything is set, press the "Next" button.
4. On the "Choose a Destination" window, in the Destination box, choose Microsoft Excel item for exportingdata from SQL Server to Excel. Choose the Excel file path and version as you need. Then, click the "Next" buttonto continue.
5. On the Specify Table Copy or Query screen, you can choose "Copy data from one or more tables or views".Then, click "Next" to go on.
6. In the "Select Source Table and Views" window, you can choose one or more tables and views from which you want to export SQL Server data to an Excel. Click the "Preview" button to preview which data will be generated to an Excel file. Click the "Next" button to move on.
7. Here, on the "Save and Run Package" window, check the "Run immediately" and click the "Next" button.
8. Onthe "Complete" Wizard window, you can check all thesettings set during of exporting process. If everything is right, click "Finish" to start exporting SQL database to Excel.
9. You can view the exporting process in this step. When it completes, click on "Report"drop-down button.
10. You can choose "Save Report to File..."(including Excel, Notepad) to save the report. Here, you need to choose Excel as the target file format.
How to Repair SQL Server Database
SQL Server Management Studio (SSMS)offersthe Import and Export Wizard feature toexportdata from one data source to another data source.Namely, you can apply this function to export SQLtoExcel. For exporting SQL Server database to CSV, link to check the details.
Step 1. Close the SQL Service in Task Manager or SQL Server Manager Console.
Step 2. Run Qiling MS SQL Recovery, click "Browse" (the two dots) or "Search" to choose the location where you saved your corrupted database. Afterward, Click "Repair" to start analyzing the specified database.
Step 3. When it has done, choose the files you want to repair and click "Export".
Step 4. Turn on the SQL service, you can choose to export the repaired files to database to export it as SQL scripts.
Solution 2. Using the Data Connection Wizard in Excel
Another way to exportdata from SQL Server to Excel file is to use the Data Connection Wizard dialog in Excel.
1.Launch Excel, open an Excel file in which you want to export SQL Server data.On the toolbar, clickthe "Data" tab > select "From Other Sources" > choose "From SQL Server".
2.The "Data Connection Wizard" window will pop up. Enter the name of the SQL Server instance from which data need to export. In the "Log on credentials" section, choose "Use Windows Authentication" mode for the data source connection and click the "Next" button.
3.Choose the database from which the data will be exported. And click the "Next" button to proceed. On the next window, leave everything as it is and press the "Finish" button.
4.In the Import Data dialog, choose "Table" and "Existing worksheet" as the screenshot shows.Press the "OK" button. After this, the SQL Server data will be exported to Excel file.
Related Articles
- SQL to XML | How to Export Data from SQL Server to XML
- SQL Database Restore Failed Database Is in Use
- [Solved] SQL Server Error 3013/3266: Backup Database Is Terminating Abnormally
- How to Export MS SQL Server Database to CSV File