In one of our previous articles, we tried to see how to get data from Excel into an SQL table (Import Excel to SQL Database). Now, we will show you how to do the opposite – that is, how to get data from an SQL database into Excel – exporting a table. Generally, I don’t use this procedure very often because there is a significant risk of various errors when exporting to or from Excel. I prefer to import or export data to text files with separators and then, if necessary, pour the data into Excel from these text (CSV) files.

However, let’s say we want to copy data from an SQL Server table directly into Excel; this option exists, so why not use it?

How to Export from an SQL Server Table to Excel via Import/Export Wizard

1. The table we want to export is called “Test,” contains only 1 record, and is located in the “Temp” database.

1. Right-click on the database “[Temp]” -> Tasks -> Export Data. This will open the Import/Export Wizard, which will guide us through the entire process.

1. After clicking “Next,” the wizard will ask us to define the Data Source, which is the SQL Server table “Test” in the “Temp” database.

• • For the provider, it’s easiest to set it to “SQL Server Native Client 11.0.”
  • The server name is the name of the SQL server instance.
  • If we log in to the server with our domain name, leave “Use Windows Authentication.” If we have an SQL server login and password, use the second option.
  • As the last step, select the “Temp” database.

1. In the next step, we similarly define the Destination – the target location. In our case, it will be an Excel file named “test.xls.”

1. In the following window, confirm that you want to copy values from the table to Excel.
2. In the next window, select the table “[Temp].[dbo].[Test].”
3. Now, we’re almost done. You have the option to define the data types of individual columns and what happens if a record cannot be copied (an error occurs). You can choose to ignore the error or terminate the entire process with an error.

1. The entire process we’ve just gone through can be performed repeatedly. If you want to do that, you can have an SSIS package generated, which can be used for automation.

1. If you only want to run this task once, ignore this window and click “Next.” This will execute the task, and the data will be copied from the source (SQL) to the destination (Excel).