How to Export Data from SQL Server to Excel

As a developer, exporting data from SQL Server is a common task. In the organization, I also need to keep the data in Excel for my reference. I tried exporting, but I faced some difficulties. Later, I found a method to export data from SQL Server to Excel. Instead of manually entering every data set into Excel, SQL Server provides an option to import and export data from SQL Server to Excel sheets.

How to Export Data from SQL Server to Excel

This tutorial will cover various methods of importing and exporting data from or to SQL Server.

Export Data from SQL Server to Excel using Import and Export Wizard

To export data from SQL server to excel, follow the below steps. We will use the import and export wizard in SQL Server Management Studio.

• Below is the table that we are going to export automatically using the import and export wizard in SQL Server.

• Right-click on the database using which table is created.
• Select the Tasks option from the dropdown another dropdown will appear.
• Click on the Export Data.

• Once you have clicked on the Export Data option from the drop-down menu, the Import and Export wizard window will appear.
• Click on Next to proceed in the process.

• Once you have clicked on the Next button on the Import and Export wizard, a new window will appear asking you to Choose a Data Source
• Select SQL Server Native Client 11.0 in the Data Source.
• Select the instance from the dropdown for the Server name.
• For Authentication, you can use:
  • Use Windows Authentication, which means the username and password of your computer. It automatically picks up from the system.
  • Use SQL Server Authentication, this requests the Username and Password that was creating an instance.
• Select the database name from the dropdown and click on the Next button.

• The SQL Server Import and Export Wizard will ask for destination details. Choose Destination field in Microsoft Excel.
• Give the path to the Excel file into which you want to export the data
• Select the Excel Version according to the version of the Excel that you have installed on your system and click Next.

• Now, the SQL Server Import and Export Wizard will ask whether you want to copy all data from the existing tables or query-specific data. To import all table data, we can select Copy data from one or more tables or views.
• We will also explain how to copy data modified by an SQL query using the Write a query to specify the data to transfer option.

Export Data from SQL Server to Excel Automatically

SQL Server 2019 provides a method for exporting all data from the SQL server to an Excel worksheet. In this section, we will see how to export SQL Server data to Excel automatically using the Import and Export Wizard.

• Choose the table from the Source tab that you want to export specify the name of the table in the Destination tab and click Next

• In the next step, you can choose whether you want to save the SSIS Package or not. In this section, we will continue without saving.

• Now you will see the final overview of the operation before the execution, click on the Finish Button.

• Data has been successfully exported from SQL Server to an Excel worksheet. To exit the window, click the Close button.

• You can check that the file is created at the specified location.

How to Export Data from SQL Server to Excel using Query

At times, it is not necessary to export all the table data. Instead, we need to export data using a query. We can export query-specific data from SQL Server to an Excel worksheet in such situations.

• After selecting the source and destination of data using SQL Import and Export Wizard, the wizard shows two options. Choose the second option which says Write a query to specify the data to transfer and then click on the Next button.

• Then, we need to specify the query that we want to implement. In our case we will execute a query to show the Name and Age of people whose salaries are greater than 100000. After writing the query, click Next.

• Now, you will see the overview of table source and destination. You can also see how the data looks after the query is implemented by clicking on the Preview button. Then click Next.

• Run immediately is checked by default, click on Next button.

• Now, all the configurations are complete. You can click on the Finish button to start the Wizard

• If all actions are successfully completed, your query is implemented and the data is exported. You can close this window.

• Navigate to the destination path, and you can see that data is exported successfully

How to Import Data from SQL Server into Excel using Data Connection Wizard

We can also import data from SQL Server to Excel using the Data Connection Wizard in Excel. The benefit of using this method is that we can get live data from SQL Server.

Live data means that the changes made in the SQL Server database will be reflected in the Excel worksheet also. We will demonstrate this with the help of an example

• Open MS Excel and create a new worksheet or open an existing worksheet into which you want to import the data from SQL Server
• Go to the Data tab and click on From Other Sources and then From SQL Server, as shown in the image below.

• Enter the name of the Server instance that you have created in the Server name field. You can use either Windows Authentication or SQL Server login credentials to make a connection with the database. After inserting the required details, click Next.

• Now select the database and the table that you want to import. You can also import data from multiple tables by checking the option Enable selection of multiple tables.

• Save the Data Connection File. Add a description to it to store more information about the connection. It will help you to know the information about the connection when you connect to the database again. Click Next.

• A small window will appear where you can decide how you want to view your data in the Excel Workbook. You can import the data either in an existing worksheet or in a new worksheet. Select the desired option and then click OK.

• Now you can see, your data is imported successfully from SQL Server.

• As mentioned above, this type of connection is a live connection to the database. Try to change the data in SQL Server. You will see that the changes will be reflected in the Excel worksheet also. Let us see an example
• Run the following query in the SQL Query Window in SQL Studio Management

update Employees set Age =20 where [Employee ID]=1;
select * from Employees;

Here is the implementation of the above-mentioned code snippet.

• Changes are made in the table. Now refresh the Excel worksheet where you have imported the data or open it again. You will see the same changes in the imported data.

Export Data from SQL Server to Excel using Stored Procedures

• There is a method in SQL Server called OPENROWSET method, which is used to export data from SQL Server to Excel
• OPENROWSET method has some requirements that need to be fulfilled before we can use this method
  • Administrator permissions to SQL Server Management Studio.
  • Enable “Show Advanced Options”
  • Enable “Ad Hoc Distributed Queries”
• Run SQL Server Management Studio as Administrator.
• Write the query following query to enable these options.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries',1;
RECONFIGURE;
GO

• Give permissions to the Microsoft.ACE.OLEDB.12.0 driver by executing the following command

EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1

• After successfully running the above commands, you have to create an Excel sheet with the same fields which are there in the database table.

• Now you can write the query to export data from SQL Server to Excel.

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Blades\Desktop\exported_data.xls;','SELECT * FROM [Sheet1$]')
Select * from Employees;

• Remember to set the Database path, Excel file name, Excel worksheet name, and database name in the above query
• You may encounter the following error while executing the query
• This error states that the SQL Server is running on 64-bit architecture, but Microsoft Access Database Engine only has 32-bit drivers installed.

This means you have to download the 64-bit Access Database Engine.

You can download the 64-bit version of Access Database Engine from the specified link.

Install Access Database Engine and this will install the 64-bit version of Microsoft.ACE.OLEDB.12.0 driver.

Now run the query again

INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Blades\Desktop\exported_data.xls;','SELECT * FROM [Sheet1$]')
Select * from Employees;

• Now you can see, the query is run successfully

• You can open the Excel file and see if the data is exported.

• You can use this OPENROWSET method in your own stored procedure and export data from SQL Server to Excel.

Conclusion

In this tutorial, we learned about the various methods of exporting the data from SQL Server to Excel. Try this and export the data from your SQL Server.

You may like the following sql server articles:

• SQL Server Substring Function
• SQL Server Replace Function + Examples
• SQL Server Convert String to Date