Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stixoffire
Starting Member
17 Posts |
 Posted - 2009-05-05 : 16:16:33
|
| I am not sure if this is posted in the correct forum, but I have a really difficult issue I am trying to solve.I have a FoxPro DB that I must import from - it contains info for products - the only unique identifier is the SKU (which is assigned per SIZE of the product, not per product). So that table looks like ProdName, type, Cat, sku .. and there is a price table tied per sku, with a size table per skuI am trying to organize the structure of an SQL web DB with two related tables for Product. ProductMasterTable - containing information that corresponds to the product regardless of size, such as name, description , category and type. The second table would contain information such as size, price, sku, etc.. The problem is that in the FoxPro table the Names of the products have been appended with info pertinent to that individual sku only.Does anyone have any idea how I might extract from these foxpro tables the "masterProduct" from the unique sku (detail) items - so I can have a manageable database. We are not talking about 100 items , nor a one time import process - Otherwise I would just tell them to manually do it. There are more than 4000 products in various sizes - from 1 size to 6 sizes. This would be occurring on a weekly basis.I thought to try to get a list of the "Appended" Product Name information to strip that out as I process row by row - thereby giving me a more unique product name field (not perfect) - but then as I did this - I would need to make sure MasterID parameters either match a preexisting MasterID (if not it might be a new one) Then Id entify and relate the details to that masterID.Anybody with Ideas..?? |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2009-05-05 : 16:52:48
|
| I can't tell what specific issues you are having trouble with. We certainly can't recommend specific ideas without knowing all your requirements, your datamodel, etc. But (very) generally speaking here's one possible overview plan:create "staging" tables which match your foxpro structures.truncate then import into those first. I'd suggest no unique constraints of any kind on those staging tables.Then start building a "transformation script" that will maintain your normalized objects based off your staging tables. You can build in all the business rules necessary to deal with transforming IDs, as well as typical imported data problems like conditions that would result PK violations, orphaned child rows, etc.Ideally you can create an SSIS package which will control the entire process.Be One with the OptimizerTG |
 |
|
|
stixoffire
Starting Member
17 Posts |
Posted - 2009-05-07 : 04:34:42
|
| Well I think you are follwoing what I am trying to achieve pretty well. I had been performing in .NET a translation of the data row by row (hard code) into some temp tables to get many other parts of the data properly linked, and imported. (Just as you said with otu declaring any unique columns PK etc..) How can I do such a thing as you mention with SSIS, I did not think I could strip away info from rows conditionally - is there a good link to SSIS How To: ? |
|
|
|
|
|
|
|
|
Importing from a non-normalized database