Skip to content

MS SQL

SQL Server Data Migration Example for Non-SQL Data Sources

Overview of SQL Server Data Migration

Migrating data from non-SQL data sources into SQL Server is a commonplace scenario encountered where we need to extract data from a source which is not a relational database (e.g. a flat-file or a spreadsheet). It can even be the output from a biometric system coming in the form of ASCII codes.

So, we will need to consider three building blocks to process them

  1. Extract
  2. Transform
  3. Load

i.e. ETL as per our requirement.

Choosing a Migration Strategy

Migration strategy is determined mainly based on the following approaches –

  1. Type of data source – Based on the source of data, we will get the idea of which methodology will be involved to process them for making ready for importing into the destination system,
  2. Frequency of migration – The periodicity of the migration (e.g. only one-time or daily or weekly) dictates the modularity and scalability that we need to think of while planning. For example, if it is a one-time affair, we can resort to simply importing the data using “Import Data” wizard present in SQL Server or even a “bulk insert”. But when it comes to a daily affair (or even weekly) we will need to give a detailed thought on the maximum volume of data, maximum possible size of individual fields that can come and whether we will need to have a detailed logging of the records that are imported or failed.
  3. Non-standard data-types – special considerations have to be provided in cases where we need to import data into non-standard data-types (e.g. date-time or xml column).

Choosing Suitable Driver for Migration

While designing a solution for data migration from flat file data sources, utmost attention must be given on choosing of database drivers if we want to do it programmatically. For example, while importing data from an Excel spreadsheet containing a numeric value with leading zeroes in a column; while importing it might so happen that those leading zeroes are truncated. In similar kind of situations it is always advisable to read the file contents programmatically (say, using .Net FileStream objects) and process them as per requirement.

Some Simple Approaches to Data Migration with Example code

Using Bulk Insert:

In the below example, a basic example of BULK INSERT has been shown. As the destination table structure and the data source is having the identical number of columns and in same order we can use this as-is. But if the number differs or the ordering of column changes then we need to add a FORMATFILE parameter inside WITH clause.

Using BULK INSERT option to import data from CSV data source

Using BCP command-line utility:

BCP (Bulk Copy Program) is a command-line utility which is more advanced option from BULK INSERT as it gives the flexibility to specify a query inline to select any filter condition based on which data can be imported or exported.

But since this is a command-line utility, we may encounter the error (shown below) while running it through xp_cmdshell extended stored procedure.

Click to enlarge

xp_cmdshell is disabled by default

To verify whether xp_cmdshell is turned on or off we can use the following query:

Querying sys.configuration

Querying sys.configuration

 

In order to make it enabled, the following steps have to be performed.

Changing sys.configuration system table through sp_configure system procedure

Changing sys.configuration system table through sp_configure system procedure

After running bcp utility successfully, data get copied into SQL server as shown below.

Running bcp utility with prompts; –c for importing character data, -r for specifying rowterminator, -t for fieldterminator, -S for SQL server instance where data will be copied, -U for SQL server username and –P for SQL server password.

Running bcp utility with prompts; –c for importing character data, -r for specifying rowterminator, -t for fieldterminator, -S for SQL server instance where data will be copied, -U for SQL server username and –P for SQL server password.

We have seen two ways to migrate data from Non-SQL data sources to SQL Server table. In the next article we’ll see one more method of migration known as Import Data wizard. Did you like this article. Please spread the word about it.

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)