Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    7

    Unanswered: Insert records into unusual table

    This problem goes against everything that I've been taught and have always read about, so please don't think I designed this.

    I'm trying to write a procedure to insert orders from 3 tables into one table to import into our mail order system. That's not a problem.
    The problem is the mapping.
    The basic design:

    tblOrders (order_id, customer_id, shipto_id, billto_id, card_num, etc.)
    tblOrderItems ( item_id, item_price, item_name, etc.)
    tblContacts(contact_id, f_name, l_name, card_no, etc.)

    BUT, I have to export to this table format.....REALLY!!

    tblOrdersToExport (
    order_id, contact_id, fname, lname, address, city, state, zip, country,
    ---- now here's the good part
    product1, quanity1, price1,
    product2, quanity2, price2,
    product3, quanity3, price3,
    product4, quanity4, price4,
    product5, quanity5, price5,
    order_continued
    )

    The order_continued is flagged if the order contains more line items than product fields (5), and the row doesn't contain any information except product#, quanity#, price# and the order_continued flag.

    I think I've got the right idea. I'm playing with cursors and some procedures, but I'm running into problems when I have more than 5 items and have to insert into this very unusual structure.

    I know this is strange, but it's what I have to work with and i have no way of changing the db structure or table layout since it's part of a program that was purchased and there's not way that I can recode anything.

    If someone can make sense of what I'm trying to do, I can post an example if it will help. I wanted to keep the length of this message short and it's very hard to explain sense it doesn't make much sense.

    thanks
    Randy

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Insert records into unusual table

    Can you post the code you've written so far, and indicate where it runs into problems? It sounds like you just need to loop through the items and whenever you get to a multiple of 5 + 1 start a new record - i.e. at item 6, 11, 16, ...

    Whoever designed their database should be shot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •