Data Migration - Quickly Inserting New Data

I recently worked on a data migration project for a friend who was moving from one version of a web application to another. It was a home grown application and also required a complete migration from one database to another, in many cases from a denormalized structure to a very normalized structure. In completing this project, I had to tackle a bunch of different data moves and use a variety of techniques. This series will look at some of the ways in which I moved the data between systems.

This article looks at quickly inserting some data into tables using Excel, one of the techniques that helped in a crisis. It's an alternative to using DTS, which as you will see, wasn't an option for me.

NOTE: This is a quick and dirty technique that works for one time projects. I would not recommend this technique for repetitive tasks.

Spreadsheet Sources

How many times has someone given you a bunch of data in Excel and asked you to import this into a table? It's happened countless times to me. I guess that it's easier for most users to create a data set in Excel than it is using T-SQL or any other tool, including Access. Open Excel, you have a blank spreadsheet and can start typing.

In this case, we were mapping a series of products from one database to another. We'd already moved some of the data, but then data entry occurred on the new system, changes were made, and the mapping that I'd built wasn't available. Since I couldn't determine how to map an old product to a new one, I had to ask someone else to assist and make the business decisions about how product 12 in the old system mapped to product 54 in the new one.

Well, needless to say, I got an excel sheet that looked like this:

No headers, no nothing. A quick conversation showed me that the first number was the old product number and the right number was the new one, but that was all the info I got. OK, no problem. Usually I use DTS to import something like this, but when I fired up Enterprise Manager on my old system and clicked "New Package", I got an error. In fact, I got about 5 before the package designer opened with only Access and Flat File connections available. But that's another story.

I was onsite, trying to get this done quickly since we were in the middle of testing and I needed to move more data across. I could have borrowed a computer from a developer, but that would have interrupted their work as well as been a nuisance. So I thought fast.

I'd seen this technique used by my buddy Vern when I was at J.D. Edwards, so I can't take credit for it, but I did remember to use it here.

Quick and Dirty

The first thing that I did was go to the third column on the spreadsheet and add the following:

Note that I'm only showing part of the spreadsheet. The actual data was a few hundred rows long. Also, there is a space after "select". Most of you probably know what I'm doing here, so I'll run through the steps and then explain.

Next I went to the fourth column and added a forumla that looked like this:

=C1&A1 & "," &B1

What this formula says is to take column 3 and append column 1. Then append a comma and then append column 2. If you now copy and paste the 3rd and 4th columns (C and D) all the way down, you get:

As you can see, I've "built" a bunch of insert statements for my productmap table. I then quickly created this table and then cut and pasted the "D" column into Query Analyzer, pressed ALT-X, and my data was inserted!

Conclusions

Not the cleanest or most elegant technique. But it works, it's quick and dirty, and will work whenever you have any sort of QA tool, even if you don't have Enterprise Manager. The total time for me to build this insert was a couple minutes, about the same as DTS. I'm not sure which is better, but they both work about the same.

I welcome your comments or alternatives to using this technique below. Let me know if you like this or not and read the other articles in my "Data Migration Series".

Steve Jones

©dkRanch.net June 2004

Return to Steve Jones Home