SQl Server Management Studio (SSMS) provides functionality to export Database table data into excel file sheet. It is possible to save table data into an excel file through SQL Server using wizard.
First way: Very simple way is just select all results of any table by right click, select all, then again right click and select Copy with Headers and paste it in your excel sheet.
![1]()
![2]()
![3]()
Second way: To export data from one or multiple tables in either custom way or just export all data we have option in SQl Server Management Studio (SSMS): I have an existing database with tables into it.
- Right click on your database, then select Task and click on Export Data.
![4]()
- Click on Next in SQL Server Import and Export Wizard:
![5]()
- In Choose a Data Source: Select SQL Server Native Client 11.0 and click on Next.
![6]()
- In Data Source: Select your SQL Server login authentication and fill username and password. Now select your database and click Next.
![7]()
- In Choose a Destination: Select your desired destination, I am selecting Microsoft Excel and click Next.
![8]()
- In Destination: Select your destination file from location and click on Next:
![9]()
- Select Table Copy and click on Next:
![10]()
- Select the source tables for export, you can do mapping by clicking on Edit Mapping option, and you can click on Edit SQL to change order or datatype of table. Click Next.
![11]()
- In Review Data Type Mapping you have option to check or uncheck use global datatypes, if fail error is showing you can change it to ignore by selecting On Error option, Click on Next.
![12]()
- In Save and Run Package: Select Run Immediately and click Next:
![13]()
- Execution Status: Execution successful, click Close.
![14]()
Now open your excel file in which tables were exported, I had saved the records into
Data file:
![16]()
You can see both the tables (
Product1 and
Trainee1), the selected values from my database are exported into
Data excel format file within 2 sheets.