lookup tables

Hi I have the task to insert values to table with Foreign keys in it.
Example
main table products
prod id prod name categ.id
1 cheese 2

lookup category table

cat id cat name
1 household
2 food

how do i insert source
1 cheese food


i understand i need to have a lookup to look for food in category table and return id no 2
and then use this id no in insert statement to main table
if cheese would not be yet in category table then I would need to insert it to it(what does lookup return then ? null? can I say If isNull(?)?
I have an idea about the logic but I have never used DTS or any scripring language so I am not sure how to do it
looking forward to any advice

sorry error in previous mail:
I meant if food is not yet in category table(not cheese)
ooo clarity is a gift

You have to write a stored procedure that will read the source in a cursor, then fetch rows one by one, check if category exists, get ID if exists else insert category and use new category id to insert into products table.

To check existence you can use

select @categoryidvar=categoryid from category where name='food'

if @categoryidvar is null
then ....