Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

This may be obvious, but I've been banging my head for a while... please point me in the direction of an article if there is one.

I'm importing a series of Excel files into a SQL database.

They will have a list of text items with a value:

Name   |   Value
----------------
Name1  |   100
Name2  |   140
Name3  |   200

The target database has a lookup table of "Name" mappings.

What I want to do is find a corresponding NameID for a row, and put that into a column (this is the easy part), however, if there is no corresponding entry in the Name table it should insert the row and get the new ID.

Is there an accepted way to do this? I'm looking at low volumes, so I don't need it to be particularly performant, just simple and easy to follow.

share|improve this question
    
Assume Name3 does not exist in the reference/lookup table. Is it possible that Name3 would appear multiple times in the same Excel spreadsheet? –  billinkc Mar 20 '13 at 15:00
    
no, I would do a sort/dupe check. –  Martin Mar 20 '13 at 15:58

1 Answer 1

up vote 2 down vote accepted

Option 1

It's been awhile since I have done it but basically this is what I did.

  1. After the lookup, do a conditional split.
  2. Split the NULL values off on their own path.
  3. Use an OLE DB Command to insert your rows. Be careful here because you don't want to insert the same row over and over again.
  4. Re-lookup your value (or get it back from the OLE DB Command if you can).
  5. Use a Merge to merge the two data flows back together again.

Option 2

Do it with 2 data flows. In the first one, find all of your data that doesn't match and do the insert. In your second one, do the lookup.

The latter option may be simpler for your purposes.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.