How to Automatically Export Data from SQL Server to Excel

"Hello, I am new to SQL. We utilize SQL queries in SQL Server 2016, and then we export the results to Excel. Could you please advise me on how to automate the process of exporting data from SQL to Excel? As I no longer wish to perform this task manually."

If you encounter the same issue mentioned above and are seeking a method to export SQL Server data to Excel, worry not. This page presents two possible ways to assist you in automatically exporting data from SQL Server.

Two Ways to Export Data from SQL Server to Excel

You can use the SQL Server Import and Export Wizard and the Data Connection Wizard in Excel to export data from SQL Server to Excel. See the details below.

Solution 1: Utilize the SQL Server Import and Export Wizard

1. Open SQL Server Management Studio and connect to the database.

2. Navigate to "Object Explorer," locate the server database you wish to export to Excel. Right-click on it and select "Tasks" > "Export Data" to export table data from SQL. This will open the SQL Server Import and Export Wizard welcome screen.

3. Next, click on the Data source dropdown button to select the data source you wish to copy. In this case, choose "SQL Server Native Client 11.0". In the Server name dropdown, select a SQL Server instance. Under the Authentication section, pick the authentication method for the data source connection. From the Database dropdown, select the database from which data will be copied. Once all settings are in place, hit the "Next" button.

4. On the "Choose a Destination" window, in the Destination box, select the Microsoft Excel option to export data from SQL Server to Excel. Specify the Excel file path and version as required. Then, click the "Next" button to proceed.

5. On the Specify Table Copy or Query screen, select "Copy data from one or more tables or views." Click "Next" to proceed.

6. In the "Select Source Table and Views" window, choose one or more tables and views from which you wish to export SQL Server data to Excel. Click the "Preview" button to see a preview of the data that will be generated in an Excel file. Proceed by clicking the "Next" button.

7. On the "Save and Run Package" window, check the "Run immediately" option and click the "Next" button.

8. On the "Complete" Wizard window, you can review all the settings configured during the export process. If everything is correct, click "Finish" to commence exporting the SQL database to Excel.

9. You can observe the exporting process during this step. When it finishes, click on the "Report" drop-down button.

10. You can select "Save Report to File..." (including Excel, Notepad) to save the report. In this instance, choose Excel as the target file format.

How to Repair SQL Server Database

SQL Server Management Studio (SSMS) provides the Import and Export Wizard feature to export data from one data source to another. Specifically, you can use this function to export SQL to Excel. For exporting a SQL Server database to CSV, refer to the link for detailed information.

Step 1. Stop the SQL Service in Task Manager or SQL Server Management Console.

Step 2. Launch the MS SQL Recovery tool, click "Browse" (the icon with two dots) or "Search" to select the location where your corrupted database is saved. Then, click "Repair" to initiate the analysis of the specified database.

Step 3. When it's done, select the files you want to repair and click "Export".

Step 4. Turn on the SQL service, and you can opt to export the repaired files to a database or as SQL scripts.

Solution 2: Using the Data Connection Wizard in Excel

Another way to export data from SQL Server to an Excel file is to use the Data Connection Wizard dialog in Excel.

1. Launch Excel and open an Excel file where you want to export SQL Server data. On the toolbar, click on the "Data" tab, then select "From Other Sources" and choose "From SQL Server."

2. The "Data Connection Wizard" window will appear. Enter the name of the SQL Server instance from which you need to export data. In the "Log on credentials" section, select "Use Windows Authentication" for the data source connection, then click the "Next" button.

3. Select the database from which the data will be exported. Click "Next" to proceed. On the following window, keep all settings as they are and press the "Finish" button.

4. In the Import Data dialog, choose "Table" and "Existing worksheet" as shown in the screenshot. Press "OK". This will export the SQL Server data to an Excel file.