Import and Export Data in SQL Server

Importing or exporting data is a very frequently performed task to copy the data between different databases. When I say different databases, it can be the databases which is present in the same server or on different servers.
There are many ways to import or export data, like using Queries, in-built tools, 3rd party tools, etc...
One of the easy way using the inbuilt tools is by using the "SQL Server Import and Export Wizard"
Using SQL Server Import and Export Wizard, it is easy to select the type of source and destination and other options like Database, table, etc...

To export data from a database in SQL Server using SQL Server Import and Export Wizard to an Excel Sheet,

1. Login to the Server using SSMS
2. Right Click on the database from which you need to export data
3. Choose "Tasks" and Click "Export Data..."
   
4. In the "Welcome" screen, choose "Next"
5. There is a wide range of options available for choosing your source system, In this case the source will SQL Server.
   
   Make sure to Choose "SQL Server Native client" and then verify the Source System Authentication and database settings and Click "Next"
   
6. Now in the next screen, you need to choose your destination.
   There are many options available similar to the source system. I will choose to export the data to Excel. Once the Destination is chosen to be Excel, you will have to provide the path to save the file and you can also choose the Excel version.
   
7. In the Next screen, you can either write query to fetch the data or choose the data from available list of tables. In this screen, I will choose to copy data from the tables.
   
8. In the next screen, choose the tables or views from which the data needs to be exported.
   
   If you click on "Edit Mappings..." you will be able to edit the Column's Mappings, Choose the destination Data Type and other options.
   
9. In the next screen, the Wizard allows you to either run the package or to save the package or do both. I will choose both the options (Run and Save Package)
   You can save the package in either file system or within the SQL Server.
   
10. Specify the location for saving the package. The below screen will be different if you choose to save in SQL Server.
    
11. Click Finish to start the package execution
    
    
12. Once the package execution completes, the data can be viewed in the excel sheet.
    
    

Permissions Required by Import and Export Wizard.

To run the SQL Server Import and Export Wizard successfully, there are few basic permissions required.

• Permission to connect to the Source and Destination Server, databases and File system
• Permission to read data from the Source database or file
• Permission to write data to the Destination database or file

These are the basic permissions and this will vary if your choose to save the package to file system or SQL Server database, etc...