Data Migration, ID problem

Hi all,

I'm working on the data migration section of my project at the moment. The customer I'm working for has an existing system of storing client information in an Excel file. Now, I want to move all this information into SQL Server so my new webapp can access the information. The problem is, for the client details, I used an ID system. For example, each piece of client information can be selected from a list. In the client table, this is stored as an ID. Then I have a ReferenceItem table where the IDs are mapped to descriptions. So my Client table has numbers in it, that map to descriptions in a different table.

Now, the existing Excel files have the descriptions, not IDs. What I need to do is to write some kind of script that will automatically go through the table created after I imported the Excel file, check each description and find it's ID and replace that description with the ID.

Now, I don't even know where to start on this one so if anyone has any pointers, could they please let me know?

Thanks
Eoin

You need to look into Data Transformation Services (DTS) which you can access from the Enterprise Manager. DTS will allow you to import data into your database from Excel, and to define transformations on the data during the import.

You can find more information in the Books Online.