Search code examples
sqlzipcode

Quickest way to fill database table with unique "pairs" (zipcodes)

Backstory: To help my app quickly calculate the crow-flies distance between two zips (and also Google Map's distance..), I am storing the data in a table with essentially three columns: ZipCode1, Zipcode2 and Distance.

I have another table in my database named Zipcodes that contains all U.S. zip codes.

I would like to "pre-fill" my distance table with all unique "pairs" of zip codes. Since I don't want duplicate pairs, ZipCode1's integer value should always be less than ZipCode2.

So, that leaves me with the conundrum of figuring out the quickest/best way to do this. Bonus points if there's also a way to re-run the method to just add the missing pairs.

If I was coding this in C#, it would be trivial write out, but would run very slowly (as far as I can tell). There must be some quicker method, perhaps via direct SQL?

Any help/ideas are appreciated.

Solution

  • Try the below query, which should generate all the pairs and insert them all into target table in one go:

    INSERT INTO ZipcodePairs (Zipcode1, Zipcode2)
SELECT z1.Zipcode, z2.Zipcode
  FROM Zipcodes z1
  JOIN Zipcodes z2 ON (z1.Zipcode < z2.Zipcode);