Some of the data sets that I'm working with are massive (some in the millions) with dozens of different vendors. The way the data is delivered varies greatly as well. I have Central database with all the products and tables for commonly used product options and categories such as size, color, finish, material & so on. All of these tables have a single AutoNumber PK for each.
Each vendor has it's own seperate database for the purpose of prepping the data received with links to the central database for the necessary categories tables.
I import the data and append them to a single temporary table. No PK. Don't need it. The product number suffices at this stage. The NewData (temporary table) has the category fields linked to the central db. I update the data ie...format the descriptions, changing case, removes unwanted characters, spaces and so. I assign the necessary values to size, color etc.
I append the records to a final products table in each vendor's db which has an AutoNumber PK where only new records will be added or updated for pricing.
From there I append the data to the central db which has an autonumber PK in each of the table but storing the Autonumber PK and VendorID from each vendor's product record.
When you're building a house you start at the foundation and work your way up once peice, one section at a time.
So that being said....What's in a name?
Autonumber or no, PK or no, how do I go about splitting a delimited field (no matter which field) and creating records for each in a another table? The source field and target field (or even table names for that matter) may be different in different dbs but find/replace works great for that.
ImageName
abcd, abcf, efg.jpg
TO (in another table):
ProdNo abcd.jpg
ProdNo abcf.jpg
ProdNo efg.jpg
OR (I can update the extension later):
ProdNo abcd
ProdNo abcf
ProdNo efg
I would truly appreciate some guidance, a starting point to go from.