Home » SQL Server Migration » Modus Operandi to Import and Export SQL Server Database

Modus Operandi to Import and Export SQL Server Database

Stacy Brown | Modified: 2022-05-20T15:46:01+05:30|SQL Server Migration | 4 Minutes Reading

The simplest way to import and export SQL Server database, is by using the Import and Export Wizard. This wizard can copy the data from and to any kind of data source, which has a managed .NET Framework or an OLE DB provider. It is a part of SQL Server Integration Services or SSIS. The users can make use of SSIS for building extraction, transformation, loading packages and for creating packages to export SQL data to MS Excel spreadsheets or a different SQL Server database.

In this blog, we will describe the process to both export and import data from and into SQL Server 2014 with the help of Import Export wizard.

Have a Look  

If you are looking for a safe and automated way for SQL database Migration then, you must try SysTools SQL Migrator . Download its Free Edition to export 25 items. Supports SQL Sever 2019 and below versions.

Purchase Now
100% Secure
Free Download
100% Secure

 

Procedure to export SQL Server Database to Excel

Step-1. Open SQL Server 2014 Management Studio.

1) Open Management Studio

Step-2. Connect to the database engine server.

Step-3. Click on Databases and expand it.

Databases

Step-5. Right click on the database that has to be exported and click on Tasks option from the drop-down menu.

Tasks

Step-6. From the menu, select the Export Data option.

Export Data

Step-7. Click on Next to proceed from the Welcome screen.

Step-8. In Choose A Data Source Window, enter the following credentials:
Data Source: SQL Server Native Client 11.0
Server Name: Enter the name of the server
Authentication: Select the appropriate authentication for the connection
Database: Choose the database, which has to be exported.

Step-9. Click on Next to proceed.

Choose A Data Source

Step-10. In the Choose a Destination window enter following credentials: Destination: Select Microsoft Excel from the options.
Excel File Path: Browse the location of the Excel file in which database has to be exported.
Excel Version: Select the Excel version supported on your system.

Step-11. Click on Next to proceed.

Choose a Destination

Step-12. In the Specify Table Copy or Query window, select the Copy data from one or more tables or views. Click on Next to proceed.

Copy data from tables

Step-13. In Select Source Tables and Views window, select the table that you want to export.

Select Source Tables and Views

Step-14. In the Review Data Mapping window, you will see different data types mapped between the source and the destination.

Review Data Mapping window

Step-15. In the Save and Run Package window, click on Run Immediately option to run the SSIS package immediately. In case, you want to save it click on Save SSIS Package option. Provide the password if required. Click on Next to proceed.

Save and Run Package

Step-16. The Complete the Wizard window gives a preview of the choices made by the user. Select Finish option to run the SSIS Package.

Complete the Wizard

Step-18. Once successful, a complete report of the process is generated. Click on Close to end the process.

Close

Procedure to Import Excel Spreadsheet Data into SQL Server Database

Step-1. Open SQL Server 2014 Management Studio.

Open SQL Server Management Studio.

Step-2. Connect to the database engine server.

Step-3. Click on Databases and expand it.

SQL Databases

Step-4. Right click on the database that has to be exported and click on Tasks option from the drop-down menu.

Step-5. From the drop-down menu, select Import Data option.

Import Data

Step-6. Click on Next to proceed from the Welcome screen.

Step-7. In Choose A Data Source Window, enter the following credentials:

    1. Data Source: Enter Microsoft Excel as the database source
    2. Excel File Path: Browse the path of the Excel spreadsheet.
    3. Excel version: Select the version of MS Excel.
    4. Click on Next to proceed.

Choose A Data Source

Step-8. In the Choose a Destination window enter following credentials:
Destination: Select SQL Native Client 11.0 from the drop-down menu.
Server Name: Enter the name of the destination SQL Server.
Authentication: Select the appropriate authentication for the connection
Database: Choose the database, which has to be exported.

Step-9. Click on Next to proceed.

Choose a Destination

Step-10. In the Specify Table Copy or Query window, select the Copy data from one or more tables or views. Click on Next to proceed.

Specify Table Copy

Step-11. In Select Source Tables and Views window, select the table that you want to export. Click on Next to continue.

Select Source Tables and Views

Step-12. In the Save and Run Package window, click on Run Immediately option to run the SSIS package immediately. In case, you want to save it click on Save SSIS Package option. Provide the password if required. Click on Next to proceed.

Save and Run Package

Step-13. The Complete the Wizard window gives a preview of the choices made by the user. Select Finish option to run the SSIS Package.

Complete the Wizard

Step-14. Once successful, a complete report of the process is generated. Click on Close to end the process.

Close

Conclusion

With the help of the above-mentioned steps, the SQL Server users can easily perform import and export operations on the data.