Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

Import and Export Wizard in SSMS -- Step by Step

  • 13 July 2010
  • Author: indupriya
  • Number of views: 7616
  • 0 Comments

In this article I will show you how to use the import export wizard in SSMS.  This article has two parts –

1.    Importing data from an external source(Excel) into the sql server database

2.    Exporting data from sql server database into an external source (Excel)

Importing data from an external source(Excel) into the sql server database

 

Following are the steps that can be followed to import data using the Import and Export Wizard, follow these steps:

  1. Launch SQL Server Management Studio and log in to your sql server.
  2. If you want to import data into a new database, create a new database by rightclicking on the databases and choosing “New Database”
  3. The following wizard appears where you give a name to the database and click ok.

 

New database

 

 4.  Then righclick on this newly created database and choose tasks and Import data

 Tasks

 

5. Then you will be taken to the import export wizard as shown below 

 Import and Export wizard

 

6.   Click next.  The next screen prompts you to choose a data source

 

 

 Data source

 

7. Choose the data source – I want to import data from an excel file.  So I choose Microsoft excel.  Notice that the data source form changes to suit the excel import.  Screenshot below

  

 

 

excel 

8. Provide the excel file path by browsing to the excel file that has the data to be imported. Also provide the excel version.  If your file contains heading columns, then tick the First row has column names.  Now click Next 

9.  The screeshot below shows you that the next step is to choose a destination

  

  

  Import destination

10.  Provide the database server name, Keep the authentication as Use Windows Authentication and choose the newly created database (this is where you want to import the data).  Click Next

11.  The following screen appears enabling you to either copy all the existing data from the file or restrict so that you can choose which data to import.

 

  Copy

  

12.  For simplicity I choose the first Copy data option and click next.  The screenshot that appears next will allow you to choose the data and check whether what you have chosen is correct by checking the source, destination

 

Importsource

 

13.  If you click on the Edit button in the above screen, that shows you the column mappings as shown in screenshot below.

 

 Column Mappings

 

 

14.  In the above option the wizard is going to create the destination table in the database.  If you click on the Edit SQL button in the above screen, it shows you the SQL query as follows

 

 

 SQL

 

 

15.  If you want to change the name of the table that is being created you can change it here and click ok. Click ok to the column mapping screen and you can click on Preview button from the Select Source Tables and Views screen.  The preview shows the data in the excel file with the mapping of the fields

 16.   Click Ok and click Next The Save and Execute Package form appears.

 

Import Finish

  

 

17.  If you want to import the data now, tick the execute immediately box.  If you want to repeat this import frequently, then tick the Save SSIS Package and choose either SQL Server or File system depending on your need.  Click Finish.  The data will now be imported into the new table created in the database you specified.

  

Exporting data from sql server database into an external source (Excel)

Following are the steps that can be followed to import data using the Import and Export Wizard, follow these steps:

 

 

  1. Launch SQL Server Management Studio and log in to your sql server.
  2. Right click on the database from which you want to export the data and choose the Tasks and Export Data as shown below

 

Tasks

 

 

 

 

 

3. The import export wizard appears as shown below. 

 Import Export wizard

 

4.  Click Next.  Then choose a data source appears with the dfault server name and database based on which database you have chosen.  Check the details and click Next

Export data source

 

5.  Then Choose a Destination screen comes up as shown below

Export destination

 

 

 

6. Choose the Destination as Microsoft Excel.  As soon as you do this the form changes to the following to enable you to choose a file name and excel version.

Excel destination

 

 

7.  Give a new file name and click Next.

8.  The next screen enables you to either copy all the data from the file or filter some data based on a query.  I will choose the first option and click next.

 

 

export copy

9.  In the next screen choose the tables that you want to export.  I have chosen one table as shown below.

Export source tables

 

 

10.  Click on the preview to see the columns and data.

 

Export preview

 

 11.  Click Next to go to the last screen.  Choose the option Execute Immediately if you want to export the data now.  If you want to do the export frequently choose the Save SSIS Package and click finish.

 

 

 

 

Export finish

 

 

 

 

 

 

 

 Preview

Print
Categories: Miscellaneous
Tags:
Rate this article:
No rating

indupriyaindupriya

Other posts by indupriya

Please login or register to post comments.