Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Querying one large table and inserting into another

    I have the following scenario:

    tbl_products
    pd_id autoincremnt primary key
    pd_name varchar
    pd_desc varchar

    tbl_supplierProducts
    pd_id int foriegn key references tbl_products
    supplier_id int foriegn key references tbl_suppliers
    pd_code varchar

    tbl_import
    id autoincremnt primary key
    pd_name varchar
    pd_desc varchar
    pd_code varchar
    supplier_id int


    tbl_products has over 1 million records. tbl_import has about 10000 records.
    I need to query tbl_products to see if each product contained in tbl_import is already in tbl_products. This query can be done on pd_name as this is a unique key.

    If a product already exist in tbl_products i need to retrieve the pd_id from this table and the pd_code, supplier_id from tbl_import and insert this as a new record into tbl_supplierProducts

    If a product in tbl_import does not match any record in tbl_products then the pd_name, pd_desc from tbl_import is inserted into tbl_products. The pd_id for this new record is retrieved from tbl_products and a new record is inserted into tbl_supplierProducts as above.

    Ok so how would i go about doing this with mysql and how long do you think it would practically take to search a million records 10000 times and then doing 10000 inserts as i need to do this frequently?

    I have a pseudocode to do the above not too sure abt creating a procedure for that though any help would be appreciated or any other technique to achive the above?

  2. #2
    Join Date
    Aug 2005
    Posts
    30

    Thumbs up

    Below Query inserts new records in supplier table for names that are already in product table.

    Code:
    INSERT INTO tbl_supplierProducts (pd_id, supplier_id, pd_code)
    SELECT tbl_products.pd_id, tbl_import.supplier_id, tbl_import.pd_code
    FROM tbl_import, tbl_products WHERE tbl_products.pd_name = tbl_import.pd_name
    Now remove duplicate data from import table:

    Code:
    DELETE tbl_import.* FROM tbl_import, tbl_products WHERE tbl_products.pd_name = tbl_import.pd_name
    Now insert new products names that are not in product table:

    Code:
    INSERT INTO tbl_products (pd_name, pd_desc)
    SELECT tbl_import.pd_name, tbl_import.pd_desc FROM tbl_import
    Now insert data into supplier table for new product names:

    Code:
    INSERT INTO tbl_supplierProducts (pd_id, supplier_id, pd_code)
    SELECT tbl_products.pd_id, tbl_import.supplier_id, tbl_import.pd_code
    FROM tbl_import LEFT JOIN tbl_products USING (pd_name)
    Check and let me know if it works for u. Cheers!

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by ashish_mat1979
    Check and let me know if it works for u. Cheers!
    Works like a charm! thanks a lot.

Posting Permissions

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