-
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
-
correction
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 ....
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|