Programmatically Updating a Table with Detail from derived from another Table
-
Hi, We are creating an ASP Database Application that connects to Sql Server 2000. The application is for associating a zip code with a warehouse. For instance a zip code for the los angeles area would go to the los angeles warehouse and a zip code for the new york area would go to the new york warehouse. We would like to enter zip code ranges into the following Wzip_Log table. Then we would like to programmatically populate the Wzip_Detail table and the Wzip table. The Wzip_Log table holds zip code ranges, where as the Wzip_Detail and the Wzip table hold individual zip codes. For instance, the Wzip_Log table might have the following record (completely fictitious) Wzip_Log_ID: 1 PostalCode_Start: 11111 PostalCode_End: 11113 CountryCode: USA WarehouseNum: 1 Operator: jdoe Time_Stamp: 00:00:00 6/1/2006 Then we would want the Wzip_Detail table to be populated as follows: Wzip_Detail_ID: 1 Wzip_Log_ID: 1 PostalCode: 11111 CountryCode: USA WarehouseNum: 1 Wzip_Detail_ID: 2 Wzip_Log_ID: 1 PostalCode: 11112 CountryCode: USA WarehouseNum: 1 Wzip_Detail_ID: 3 Wzip_Log_ID: 1 PostalCode: 11113 CountryCode: USA WarehouseNum: 1 The Wzip table would be populated exactly the same as the Wzip_Detail table at this point. However, say a user later updated the Wzip_Log table as follows: Wzip_Log_ID: 2 PostalCode_Start: 11111 PostalCode_End: 11113 CountryCode: USA WarehouseNum: 2 Operator: jdoe Time_Stamp: 01:10:00 6/4/2006 The Wzip_Detail table would contain an INSERT of this data (giving it a total of 6 records) whereas the Wzip table would contain an UPDATE of this data (giving it a total of 3 records) For your reference, here are the table layouts: Table Name: Wzip_Log Wzip_Log_ID int 4 (primary key, Identity) PostalCode_Start varchar 10 PostalCard_End varchar 10 CountryCode varchar 3 WarehouseNum int 4 Operator varchar 50 Time_Stamp datetime 8 Table Name: Wzip_Detail Wzip_Detail_ID int 4 (primary key, Identity) Wzip_Log_ID int 4 (foreign key) PostalCode varchar 10 CountryCode varchar 3 WarehouseNum int 4 Table Name: Wzip Wzip_ID int 4 (primary key, Identity) Wzip_Log_ID int 4 (foreign key) Wzip_Detail_ID int 4 (foreign key) PostalCode varchar 10 CountryCode varchar 3 WarehouseNum int 4 I think there is probably some way to do this prog