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
i.e. ETL as per our requirement.
Choosing a Migration Strategy
Migration strategy is determined mainly based on the following approaches –
- 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,
- 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.
- 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 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.
To verify whether xp_cmdshell is turned on or off we can use the following query:
In order to make it enabled, the following steps have to be performed.
After running bcp utility successfully, data get copied into SQL server as shown below.
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.