pd_id int foriegn key references tbl_products
supplier_id int foriegn key references tbl_suppliers
id autoincremnt primary key
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?