Sometimes you need to export data from SQL Server tables, views or the result of any T-SQL query into excel file, reason could be anything such as MIS team need Sales data in excel file so that they can prepare an MIS report and share to management team, or may be client can ask for any data in excel file, or so on..
SQL Server Management Studio allows you to export tables, or views data into excel file, even you can export multiple table, view’s data into excel file in one go.
Lets see how to export data from SQL Server to an Excel file step by step.
First, connect to SQL Server Instance then select database, here we have select a Adventureworks2019 database.
Now right click on database, then navigate to Tasks and select Export Data..
Once you click on Export Data.. you will see a SQL Server Import and Export Wizard dialog box opens.
Just click on Next button.
After that, a will see a Choose a Data Source screen, here you need to provide a Data source details as you are exporting data from SQL Server so your data source would be a SQL Server just provide Server name, Authentication accordingly as shown in screenshot.
After that click on Next button.
Once you click on Next button, you will see a Choose a Destination screen appears, as you want to export data to excel so the destination source would be an excel.
You need to provide a destination details such as location where to copy data, excel file name, its version as shown in below screen.
Note that here we have given a name to excel file that is Export_to_Excel.
Once done with that, just click on Next button.
Next, it gives you an option whether you want to copy one or more tables, views or copy the results of a T-SQL query from the data source.
So here you can choose any one option as per your requirement as follows:
If you want to choose tables, or views from database and then export the data into excel then select first option, or if you want to write a T-SQL and wish to export the query result into excel file then can select second option.
After that select Next, Lets select the first option.
As you have selected a first option, so it asks you to choose table or more tables and view those data you want to export into excel.
Also you can see that it displays the Source name that is your SQL Server instance name, and Destination that is the location of excel file where your data is going to be export and save. Also you can see the list of available tables, and views in database Adventureworks2019, and you can choose any of them that you want to export.
Lets select the tables, or views those data you want to export in excel file.
Here we have selected two tables named as Address and Department as shown in below screen.
You can also click on Edit Mappings.. to see and modify the column mappings for destination then you can modify their data type size, nullability and other, just double click on any column in Mapping Grid to and select new mapping from dropdown.
If you do not want to modify column mapping, just click on cancel button.
After that, click on Next button.
After that, you will see a Review Data type Mapping screen appears, Here you can review you data type mapping, just select the table and review mapping.
If everything is fine just click on Next button, else you can go back by clicking on Back button to edit mapping again.
After that a Save and Run Package screen appears, if you want to save that package for re-use you can click on Save SSIS package else you can go with default check on Run immediately.
Click on Next button.
Once you click on Next, you will see a complete the wizard screen which lists all the steps that will be taken, just click on Finish button.
Once you click on finish, you can see it has started the operation, you can see each task’s details with status.
Once it is done, you will see the success status with number of rows transferred successfully.
Now you can go to the location where you have saved an excel file then open the file, and you will see that table’s data has been copied into excel file successfully.
You can see Excel file includes two sheets, first one that is Department which contains Department table’s data and second one is Address which contains an Address table’s data.
Also Read..
SQL Server Import .CSV file in Database
Copy or Delete files from directory in SQL Server
