I have a table with product codes and other data, the product codes
(because how the table works) can be repeated multiple times so i am
unable to make the field unique for obvious reasons.

But I have another table which i want to create a foreign key to
relate to the product code. It requires the reference key to be
unique.... so how I thought about doing it would be to have another
table which simply had one field full of the unique product codes.

And then have a procedure which would run on request to delete all
product codes from this new table and then it would "recompile" the
unique product codes into this table. so basically selecting unique
product codes from the main table and then inserting them into the
new table. The question is how would I write a procedure to do this?
as i have no experience in writing procedures, so if someone could
assist me in writing this then I would appreciate it or if anyone can
come up with a better solution.

Cheers