ISO 3166-1 Country Data SQL Script

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]



3 Comments

  • Shouldn't the CountryID be char(3)? The leading zeros are significant in the standard. E.g. 36 is different to 036 when comparing a country number in Active Directory

  • Hi Ben,
    I have never needed to use the Country Id outside of my own application so an int has always worked for me, but great point to watch out for if you work with Active Directory.

    Also, you could just do a CountryId.ToString("000") to get it into the correct format for Active Directory.

    Thanks,
    -Jeff

  • Thanks
    Very Helpful

Comments have been disabled for this content.