I have 3 tables of product and part info.

The first table is Products and has the structure

CREATE TABLE HPProductsData
	(ProductId                     INT IDENTITY PRIMARY KEY,
	 Product_Series		    VARCHAR(100),
	 Product_Number	    VARCHAR(20),
	 Product_Name		    VARCHAR(100),
	 Product_Description	    TEXT)

The parts table has the structure:

CREATE TABLE HPPartsData
	(PartId                              INT IDENTITY PRIMARY KEY,
	 Part_Number                    VARCHAR(100),
	 New_Part_Number            VARCHAR(100),
	 Keyword                          VARCHAR(30),
	 Category                          VARCHAR(30),
	 Part_Description               TEXT)

Since a part can appear in more than one product, and a product can have multiple parts, I created a table to hold the Product/Part relations:

CREATE TABLE HPProductsPartsData
	(RowId           INT IDENTITY PRIMARY KEY,
	 ProductId      INT NOT NULL,
	 PartId           INT NOT NULL)

All 3 tables have indexes on all non-Text fields.

The user needs to be able to return a dataset which contains the product and all parts for it by entering any combination of Product or Part info. After all the data is imported I expect there to be over 3 million records.

It would be nice to code this in one procedure, but I'm open to anything. Anyone have any ideas?