How do i export sql query results to excel automatically?

Table of Contents Show

How do I automatically export from SQL to Excel?
How do I export SQL query results to Excel?
How do I export SQL query results?
How do you automatically save SQL query results to CSV?

Updated On - 29 Jun 2022 |

Read time 8 minutes

SQL Server Management Studio (SSMS) is an excellent utility to configure, manage, and administer all Microsoft SQL Server components. There are many advantages of having SQL data in spreadsheet programs like Excel. Spreadsheets are easy to use and flexible compared to SQL. The novice users have a similar query on how they can export the SQL Database to an Excel file. Here is an example-

“I am new to SQL server database and looking for ways to export the database output to excel. I have a few database files that I urgently want to save to .xlsx or .csv. Can anyone with expertise please guide me on how to automate this process?”

Benefits of saving SQL database in an excel file

Although, the Excel file does not have the same protection level as the SQL Server, it has the essential advantages of a backup file. It can help you in the following situations-

If the user drops a column in a table by mistake, it can be recovered from the excel file.
You can overcome the hardware or media failure situations.
It can be used as a secondary backup file useful in several routine administrative needs and valuable when a natural disaster hits the on-premises location.

Now, we are going to discuss ways to export the SQL Server data to Excel. Two manual methods can be employed to achieve this task, which is described below:

Method #1 Export from SQL Table to Excel Using the SQL Server Import and Export Wizard

There exists an inbuilt feature in SQL Server Management Studio (SSMS) to export data to Excel. The detailed steps are described below:

Right-click on the database table that you would like to export. Under the Tasks, click on Export Data.
In SQL Server Import and Export Wizard, click Next. This will open up a new dialog window- Choose a Data Source. From the drop-down menu in Data sources, select SQL Server Native Client 11.0, then click Next.
In the Destination field, choose Microsoft Excel. Next, in the Excel file path field, provide the location and filename by clicking on the Browse button. Click Next to proceed.
At this point, you might encounter an error.
To solve this issue in the search field, search for the 64-bit version of SQL Server Import and Export Data.
Next, repeat steps 1-3. In the Specify Table Copy or Query window, select Copy data from one or more tables or views, and hit Next.
Next, select the source tables that you would like to export.
Click on the table and click on the Preview tab to view the table content. Click OK to exit.
In the Save and Run package window, make sure Run immediately is checked and then click Next.
In Complete the Wizard window, click Finish.
In the end, you will get a message of successful completion, as shown below:
Next, go to the location where you have exported the SQL database table as an Excel file and open the Excel file.

Method #2 Import from SQL Table to Excel Using Data Connection Wizard Dialog

Another easy way to export the SQL table to Excel is by exploiting the inbuilt Excel feature that enables us to import any SQL table. The detailed steps to do so are outlined below:

Create a Blank worksheet and open it.
On the Data tab, click the Get Data icon, select From Database > From SQL Server Database.
Enter the name of your SQL Server in the text field and click OK.
Select the appropriate option and click Connect.
Click OK to proceed.
In the Navigator window, select the database and then the table that you would like to import in Excel and click on Load.
Next, click on Close & Load icon.
Congratulations! You have successfully imported the SQL table into Excel.

Kernel for SQL Database Recovery

Just like any other database file, SQL database files are also prone to database corruption. In gruesome scenarios like these, one should rely only on professional tools such as Kernel for SQL Database Recovery. It is a feature-rich tool that can quite quickly repair your earlier corrupt/inaccessible database files, irrespective of the server version. It is also possible to create a backup of the restored databases in script form.

Conclusion

Many a time, it is easier to work with spreadsheets in comparison to SQL database tables. Therefore, it becomes necessary to export these SQL tables to Excel. In this post, we have explored two manual ways to achieve this task quickly. A damaged/ corrupted database file can hinder you from exporting from SQL to Excel; hence we recommend the user to rely on advanced tools to repair corrupt SQL databases with ease.

More Information

Recover deleted records in SQL Server
Fix SQL database recovery pending state
Recover SQL database from suspect mode
Restore SQL Server database with Norecovery

How do I automatically export from SQL to Excel?

Go to "Object Explorer", find the server database you want to export to Excel. Right-click on it and choose "Tasks" > "Export Data" to export table data in SQL. Then, the SQL Server Import and Export Wizard welcome window pop up.