How to export data from SQL Server to Excel

In several scenarios, we need to use some data which is available in SQL Server, but we need it in MS Excel. Instead of putting every data manually into Excel, the SQL Server provides an option to import and export data from SQL Server to Excel sheets. In this tutorial, we will learn various methods of importing and exporting data from or to SQL Server. Also, we will cover these topics.

• How to export data from SQL Server to Excel using Import and Export Wizard
• How to export data from SQL Server to Excel automatically
• How to export data from SQL Server to Excel using the query
• How to import data from SQL Server into Excel using Data Connection Wizard
• Export data from SQL Server to Excel using stored procedures

Here, for all the functionality, I have used SQL Server 2019 Express edition.

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

In this section, we will learn how to export data from the SQL Server database into an Excel worksheet automatically using import & export Wizard. We will be using 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 then Import and Export wizard window will appear.
• Click on Next to proceed in the process.

• Once you have clicked on the Next button on Import and Export wizard a new window will appear asking to Choose a Data Source
• Select SQL Server Native Client 11.0 in 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 as 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. We can select Copy data from one or more tables or views to import all the table data.
• We will also explain how you can copy data that is modified by SQL query with Write a query to specify the data to transfer option

Read: How to Create a Database in SQL Server 2019 [Step by Step]

Export data from SQL Server to Excel automatically

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

• Choose the table from the Source tab which you want to export and 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.

• Now data has been successfully exported from SQL Server to Excel worksheet.Click on the Close button to exit the window.

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

Read: How to create a table in sql server management studio

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. In such situations, we can export query-specific data from SQL Server to Excel worksheet.

• After selecting the source and destination of data using SQL Import and Export Wizard, the wizard show 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 which 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

Read: Advanced Stored Procedure Examples in SQL Server

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 into 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 description to it to store more information about the connection. It will help you to know the information about the connection when you will 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 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 the same changes in the imported data

In this tutorial, we have learned how to import data from SQL Server into MS Excel using Import and Export wizard in different ways. Also, we have covered these topics.

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

You may like the following sql server articles:

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

In this tutorial, we learned about the various methods of exporting the data from SQL Server to Excel. We solved some encountered errors also.

• How to export data from SQL Server to Excel using Import and Export Wizard
• How to export data from SQL Server to Excel automatically
• How to export data from SQL Server to Excel using the query
• How to import data from SQL Server into Excel using Data Connection Wizard
• Export data from SQL Server to Excel using stored procedures