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 |
|
Cwm
Starting Member
39 Posts |
 Posted - 2008-12-11 : 00:07:58
|
| Hi,I have probably asked this question before but cannot remember, If I have bare with me because I probably wasn't successful last time.First I'll explain what the problem is then a solution i am looking for.I have 2 csv files, which I already know how to import into sql, one csv file is a Loading Schedule, the other a Completed Orders file.So in my database i have a table strickly for the Load Schedule, with an autoincrementing ID, that table has 9 columns in it and thats including the autoincrement ID.Next I have a table strickly for the Completed Orders, also with an autoincrement ID, that table has 11 columns and that also includes the autoincrement ID.I am not sure if that is to many columns per table or not. When i create my tables from scratch i usually like to keep my columns to around 5 or 6 per table, and have foreignkeys to eachother. I guess this would be called normalization? anyways...So now both of these tables each have identical order numbers. What i want to is break down my tables to have 5 or 6 columns. But this is where i need some guidance, not with turning these 2 tables into smaller ones, but to foreignkey these smaller tables.So i guess what i am asking is how do insert all this data from the csv files to the smaller tables and foreignkey them altogether by using the autoincremented ID's as the foreignkeys, here is a test example...Table 1T1ID OrderNumber Tonnage CustID DateOrderedTable2T2ID T1ID DateShip CarrierID WeightIDTable3WeightID T2ID TareWeight GrossWeight NetWeightTable4T4ID ScaledIn ScaledOut TimeLeftSiteI know some of this looks off, but this would not be my final set of tables, this is just to give a kind of a graphic representation of what i am talking aboutSo when i insert into Table1, I want to take the @@IDENTIY of that and place that into Table2, etc...I am not sure how to go about doing this, i can have anywhere from 1 to 50 rows in the csv files.I sure could use some samples on how to do this. As well i know i could just as easily use the ordernumber as an alternatekey, but would that not slow down any querys against the tables? would be acceptable to keep my 2 tables as they are?if i didn't have to deal with csv files i could do this all easily within my own code, but i am stuck having to use csv file data. The csv files..I import them using bulkinsert. I am sure this may be able to be done with if and else statements, i ahve tried something similar to this a long time ago but if failed when the data was not of equal lengths..Thanks Cwm |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-12-11 : 02:09:32
|
| can i ask whats the intention behind trying to restrict tables to have only 4 to 5 columns? |
 |
|
|
Cwm
Starting Member
39 Posts |
Posted - 2008-12-11 : 11:10:03
|
| my restriction is trying to normalize the tables, instead of having 8 to 11 columns to these 2 tables. I may have the normalization thing wrong, but from what i have read it is good to have your tables to 5 or 6 columnsChris |
|
|
|
|
|
|
|
|
I need help with how to go about doing...