Results 1 to 5 of 5

Thread: Insert

  1. #1
    Join Date
    Jan 2004
    Location
    California
    Posts
    10

    Unanswered: Insert

    Here's my queries:

    INSERT INTO Bills (Cust_ID) SELECT Customers.Cust_ID FROM Customers WHERE Customers.Bill_Cycle_ID = "1"

    and

    INSERT INTO Item_Billing_Link (Product_ID) SELECT Products.Product_ID FROM Products WHERE Products.Recurring = "yes" AND Products.Cust_ID = "?"


    My question...how can I do this without having to manually go thru and enter each customer in the second query that was selected in the first query?

  2. #2
    Join Date
    Nov 2003
    Posts
    9

    Re: Insert

    Originally posted by Jen76
    Here's my queries:

    INSERT INTO Bills (Cust_ID) SELECT Customers.Cust_ID FROM Customers WHERE Customers.Bill_Cycle_ID = "1"

    and

    INSERT INTO Item_Billing_Link (Product_ID) SELECT Products.Product_ID FROM Products WHERE Products.Recurring = "yes" AND Products.Cust_ID = "?"


    My question...how can I do this without having to manually go thru and enter each customer in the second query that was selected in the first query?
    I suppose you could load the recordset into a separate array, and then use a loop to stick them back into the other table.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in general, there is almost always an sql solution which makes looping unnecessary, and this is true in this case as well
    Code:
    insert 
      into Item_Billing_Link 
         ( Product_ID
         ) 
    select Products.Product_ID 
      from Customers 
    inner
      join Products 
        on Customers.Cust_ID = Products.Cust_ID
     where Customers.Bill_Cycle_ID = '1' 
       and Products.Recurring = 'yes'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2004
    Location
    California
    Posts
    10
    Wonderful! Thanks Guys!!!

  5. #5
    Join Date
    Jan 2004
    Location
    California
    Posts
    10
    Also...in the first query when the cust_id is entered into the bills table the bill_id auto increments. I know I can use "last_insert_id" but it only grabs the first id not all of the id's if there's more than one. Is there a way to grab all id's just created in order to again insert into the item_billing_link table along with the product_id.

Posting Permissions

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