Skip to content

MS SQL

SQL Server Import Data Wizard for Data Migration

In our last post titled – SQL Server Data Migration Example for Non-SQL Data Sources we have seen data migration from non-SQL sources using:

  • Bulk Insert
  • BCP command-line utility

In this article we will see how to migrate data to a SQL Sever database using Import Data Wizard.

P.S. The steps covered in this article should work fine with SQL Server versions 2005 onwards.

Import Data wizard

The standard data Import wizard is provided by SQL Server for migrating / importing data from both SQL and non-SQL data sources. Importing data through this wizard is a 7 Step process.

Step# 1 

Create Destination Table in SQL Server. This is the table where data has to be imported from flat file data source (shown in the below image).

Creating destination table in SQL Server

Creating destination table in SQL Server

Step# 2

Right click on database name and choose “Tasks” > “Import Data”

Right click on database name and choose “Tasks”>”Import Data

Right click on database name and choose “Tasks”>”Import Data”

Step# 3

Choose flat file data source (by browsing). Un-check “Column names in the first data row” if first contains data instead of column names.

Choosing of flat file data source, uncheck “Column names in the first data row” if first contains data instead of column names

Choose flat file data source, uncheck “Column names in the first data row” if first contains data instead of column names


 

We can preview the data by selecting “Columns” option from top left pane (as shown highlighted below)

Previewing data from flat file

Previewing data from flat file

Step# 4

Navigating to “Advanced” option from top left pane, here we have the option to change the column size properties. We need to increase the size of the second column in our example as it contains larger data.

Changing column sizes while importing through “Advanced” tab

Changing column sizes while importing through “Advanced” tab

Step# 5

Choose destination SQL Server database as shown below:

Choosing destination database

Choosing destination database

Step# 6

Choose the target table where data needs to be imported.

Choosing target table where to import data

Choosing target table where to import data

Here we also have the option to see and edit the mapping between the source columns from flat file and destination columns from our table by clicking on “Edit Mapping” button. Refer the below image.

Important: In this dialog, it is to be noted that there is an option “Enable identity insert”. This is an important option while importing data into a table which has an identity column but we need to import data from a source having values for that identity column also and we need to import the data for that column. In that case we will need to turn this option on and in turn it will execute SET IDENTITY_INSERT ON on the destination table during import.

Column mapping dialog showing mapping of columns from source to destination

Column mapping dialog showing mapping of columns from source to destination

Step#7

Review the mapping information

Review of column mapping details

Review of column mapping details

Conclusion

Apart from the methodologies discussed in this article, there are some advanced techniques for migration of data (e.g. Data Transformation Services or DTS and SQL Server Integration Services or SSIS). Although SSIS performs the same tasks as DTS did in previous version of SQL Server but SSIS is almost a complete rewrite in different platform and programming methodology.

Be Sociable, Share!
    The following two tabs change content below.

    Basant Singh

    Basant is a talented software developer who is very engaged in web & cloud platforms on a daily basis. He is a contributor to codingthis.com reporting on various news and troubleshooting on various platforms. Basant is very strong in SQL and a great asset to the CodingThis community.

    Latest posts by Basant Singh (see all)