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
add comment

1 Answer

up vote 0 down vote accepted

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. Do an OLE DB Command to insert your rows (there may be something better there but I don't know it). Be careful here because you don't want to insert the same row over and over again.
  4. Relookup your value (or get it back from the OLE DB Command if you can, I'm not sure)
  5. Last but not least use a Merge to merge the two data flows back together again.

Your other option is to do 2 data flows. In the first find all of your data that doesn't match and do the insert and in the second do your lookup.

Honestly the second one may be easier.

share|improve this answer
add comment

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.