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
Step# 2
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.
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
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
Step# 5
Choose destination SQL Server database as shown below:
Choosing destination database
Step# 6
Choose the target table where data needs to be imported.
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
Step#7
Review the mapping information
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.
Basant Singh
Latest posts by Basant Singh (see all)
- Go Maps Introduction for Beginners - May 20, 2015
- Go Programming – Arrays and Slices - May 11, 2015
- Golang Switch Case with Sample Code - May 1, 2015
