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