[RESOLVED] sql server 2000 - help with insert from one table to another

[MMock]

I have a table called ldc_zip. It used to be just a look up table between ldc and zip. So there were three columns: ldc_zip_ik, ldc_ik and zip. zip was your usual USPS zip code, such as 49862. We also have a table called zip, which contains city-state-zipcode, etc. In the zipcode column of this table you would have 49862. There were actually three rows in the zip table with this zip code - Munising, Christmas and Forest Lake. We'll stick with that as an example.

We came to realize that we no longer wanted a zip code to be locked into a single ldc_ik. (LDC is Local Distribution Company, for natural gas). The zip table never had a key, but I recently (a day or two ago) gave it one. So it now has zip_ik. To the ldc_zip table I added column zip_ik and set it to NULL. This will "replace" zip code and I can use the key instead (but the zip code column will still hang around).

I hope you're still with me. This is my question. Where formerly I had one row in ldc_zip for 49862 which resolved to one ldc, I now want three. The zip_ik will index into the zip table, so ldc_zip needs a row with the zip_ik for Munising, Christmas and Forest Lake instead of just the 49862 row. Then they can each have their own LDC when that is the case. Is there a query I can run to populate ldc_zip with the other two rows? (Multiply this by all US cities - and that is why it needs to be a query). I also have to update the existing row to contain the zip_ik of one of the rows. It doesn't matter at the beginning if they all point the same ldc so I don't have to worry about that.

This is all so unclear I'm sure, and I don't expect to get any replies. But if you've managed to understand and can get me started with the queries, that would be great. Thanks.