Querying one large table and inserting into another

ozzii · #1 (**permalink**) 05-08-07, 08:41

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?

ashish_mat1979 · #2 (**permalink**) 05-18-07, 07:41

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!