SQL: script to create country, state tables
- by pcampbell
Consider writing an application that requires registration for an entity, and the schema has been defined to require the country, state/prov/county data to be normalized. This is fairly typical stuff here. Naming also is important to reflect. Each country has a different name for this entity:
USA = states
Australia = states + territories
Canada = provinces + territories
Mexico = states
Brazil = states
Sweden = provinces
UK = counties, principalities, and perhaps more!
Most times when approaching this problem, I have to scratch together a list of good countries, and the states/prov/counties of each. The app may be concerned with a few countries and not others.
The process is full of pain. It typically involves one of two approaches:
opening up some previous DB and creating a CREATE script based on those tables. Run that script in the context of the new system.
creating a DTS package from database1 to database2, with all the DDL and data included in the transfer.
My goal now is to script the creation and insert of the countries that I'd be concerned with in the app of the day. When I want to roll out Countries X/Y/Z, I'll open CountryX.sql, and load its states into the ProvState table.
Question: do you have a set of scripts in your toolset to create schema and data for countries and state/province/county? If so, would you share your scripts here?
(U.K. citizens, please feel free to correct me by way of a comment in the use of counties.)
