Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    74

    Unanswered: howto do multiple insertion ?

    hi, good day for all , i having a problem , which is actually due with multiple insertion.

    the scenario :
    - i have a customer table, customerReferenceProduct table and product table

    - customerReferenceProduct table have customerID and product_id, which actually use for keep track what are the product will show to customer

    - let say, when user enter information for new product, all information will save into new product table, then this new product id will save into customerReferenceProduct table for all customer , so end up , we need to save all row that for each customer_id but with same product_id

    below is my try sample
    Code:
    insert into customerTbl (customerid, productid) 
    select customerid from customer 
    
    //problem here, as productid is enter by customer, then how i include it ?
    my problem is, when productid is user keyin data, but customerid is from customer table, how i include the productid for insertion ?

    thank you very much for helping and guidance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    What tools(s) are you using for your front end? A web page, a custom (n-Tier) application, or something different?

    -PatP

  3. #3
    Join Date
    Mar 2004
    Posts
    74
    i'm using web page in jsp

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your "try sample" is not consistent with your explanation

    your explanation is:
    - user enter information for new product
    - all information will save into new product table
    - new product id will save into customerReferenceProduct table for all customer

    the sql you need for this is --
    Code:
    insert 
      into customerReferenceProduct 
         ( customerID , product_id )
    select customerID , 937
      from customers
    where does the 937 come from? that depends on which database you are using (which you did not mention)

    if it's microsoft sql server or access, use @@IDENTITY, if it's mysql use LAST_INSERT_ID(), et cetera
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2004
    Posts
    74
    hi, r937 , thks for reply , i'm using microdoft sql , do u mind to further explain on what is @@identity ? and also 937 ? thank you very much for helping

    in summary, the productid is user enter data , which is dynamic and not from other table ..i still can get how the new productid insert into customerreferencetable ...

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if the user is entering the productid, then you don't need @@identity

    in my query, use the productid that the user entered instead of the number 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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