Results 1 to 3 of 3

Thread: lookup tables

  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Angry 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

  2. #2
    Join Date
    Jul 2004
    Posts
    2

    correction

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

  3. #3
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    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
  •