Attention: We have retired the ASP.NET Community Blogs. Learn more >

Contents tagged with SQL Script

  • ISO 3166-1 Country Data SQL Script

    3 Comments

    I am creating a standard sign up form with one of the fields being a country drop down. So I created my Country database table and then needed to fill it with information. I found the ISO list of countries here, but then the issue was how to get that list into my Country database table. Luckily someone has already done that for me: http://vidmar.net/weblog/archive/2008/05/23/database-of-country-names-numeric-alpha-2-and-alpha-3-iso-codes.aspx

    But I also like to be able to check a script like that into source control to be able to track changes to it. So I updated the insert script to be a rerunnable insert/update script.  The script will check if a country id exists and if it does not exist, then it will insert the country otherwise it will just do an update. The insert sql script from http://vidmar.net/weblog/archive/2008/05/23/database-of-country-names-numeric-alpha-2-and-alpha-3-iso-codes.aspx really got me most of the way there so I need to give a lot of credit to him.

    The link below will get you my updated version of the country data update sql script. I also updated it to 2010 from the ISO 3166 code lists plus any changes that they have posted so it is up to date as of May 15, 2010. I will try (but no promises) to keep it updated on a regular basis. Feel free to contact me if you want to try and keep it updated for me and I will post it here.

    Click here for the country data update sql script (up-to-date as of May 15, 2010).

    You can use this sql to create the Country table:

    CREATE TABLE [dbo].[Country]
        (
            CountryId int NOT NULL,
            Iso2 char(2) NOT NULL,
            Iso3 char(3) NOT NULL,
            Name nvarchar(64) NOT NULL,
            DateCreated datetimeoffset(7) NOT NULL CONSTRAINT [df__Country__DateCreated] DEFAULT (sysdatetimeoffset()),
            DateModified datetimeoffset(7) NOT NULL  CONSTRAINT [df__Country__DateModified] DEFAULT (sysdatetimeoffset())
        ) 

    ALTER TABLE dbo.Country ADD CONSTRAINT
        pk__Country__CountryId PRIMARY KEY CLUSTERED
        (
            CountryId ASC
        ) WITH FILLFACTOR = 100 ON [PRIMARY]