How to Migrate Data to a New SQL Server DatabaseHow to Migrate Data to a New SQL Server Database

Recently, I have been working on building a custom application to replace some badly aging applications that I use to run my business. One of the biggest challenges associated with this project has been extracting data from the old applications and converting it into a format that my custom application can use.

What has made this process so challenging is the fact that I am replacing three existing applications with a single, custom-built application, and each of those three applications stores its data in a different way. Rather than trying to reuse the existing data sources, I decided to create a single SQL Server database and import all of my existing data into that database. That way, my new application can access all of my existing data from a single source. Similarly, having a single database will make it easier for me to write the code related to storing any newly created data that my custom application generates.

Of course, the big challenge is figuring out how to extract my existing data from its current sources and get that data into SQL Server. As such, I wanted to take the opportunity to show you one of the methods that I have been using.

One of the data sources I am using is a SQLite database. Initially, I assumed that migrating data from SQLite to SQL Server would be simple. In retrospect, the process was easy, but it did not work quite as I expected.

Related:An Introduction to SQL Commands, Part 1

My initial approach to this problem was to open the database in DB Browser for SQLite and export the data to a .SQL file. For those who might not be familiar with the .SQL file format, it is essentially a text file containing a bunch of SQL statements that can be used to re-create your database's tables and data. Microsoft SQL Server is able to read .SQL files, but the problem in this case is that SQLite uses a completely different command syntax than SQL Server. You can use a text editor to modify a .SQL file's contents to bring it in line with SQL Server's syntax use. To be honest, I didn't feel totally comfortable with this approach given my skill level (or lack thereof).

There are third-party tools that claim to be able to convert a SQLite database to make it work on SQL Server. I tried one such tool, but the tool failed to properly convert the database. That being the case, I ended up using a completely different approach that ultimately worked out really well.

As before, I began the process by opening my SQLite database in DB Browser for SQLite. This time, however, I chose the option to export the database tables to CSV files (this option is found on the File menu in the Export section). The drawback to using this method is that you have to create a separate CSV file for each database table. Even so, there was an advantage to using this method.

Related:Introduction to SQL Commands, Part 2: Adding, Updating Data

The advantage is that you can open CSV files in Microsoft Excel. As such, I was able to open the file and make some changes before importing the file into SQL Server. These changes involved deleting some columns that my custom application does not need and adding a few columns that I was otherwise going to have to create later on.

Bringing the data into SQL Server was a simple process. First, I connected to my SQL Server using the SQL Server Management Studio. Then, I right-clicked on the Databases tab and selected the New Database command. As you can see in Figure 1, you need only to provide a name for the new database and click OK.

Figure 1: This is the dialog box that SQL Server Management Studio uses for creating a new database.

To import the CSV file (or any other type of supported data) into a SQL Server database, right-click on the database and select the Tasks > Import Data commands from the shortcut menu. This opens a wizard that walks you through the data import process. The wizard allows you to import a variety of data types.

Related:Introduction to SQL Commands, Part 3: Retrieving and Sorting Data

Even though the Wizard provides an option for importing Microsoft Excel data, the Microsoft Excel option does not work for importing a CSV file. Instead, you must use the Flat File Source option. This option defaults to attempting to import data from a .TXT file, but you can specify .CSV as the file format to use. You can see what this looks like in Figure 2.

Figure 2: You can import data from a CSV file.

Clicking Next causes the wizard to display a screen asking where you want to copy your data. Choose Microsoft OLE DB Provider for SQL Server as the destination and then enter the credentials that you use to authenticate into SQL Server. You will also need to select the database that you want to use as a destination, as shown in Figure 3.

Figure 3: Set the destination to Microsoft OLE DB Provider for SQL Server.

To complete the process, click Next a couple of times, followed by Finish, and the data will be imported.