Database: initialization script vs. failsafe code (a.k.a. ALWAYS UPDATE vs. INSERT OR UPDATE)

Question

I was wondering... when working with a fixed set of data (say: the population of some cities in a database table CITY_NAME, CITY_POPULATION) that is subject to change which is the best practice?

1. use an initialization script to populate the table with all possible records and write code to only update the number of citizens for a given city
2. use "smart" code which can handle the creation of a new record for a previously unaccounted city and update the existing ones instead

In the first case the code will fail if the script hasn't been run before, in the second case it will certainly be a tad more complicated (and all the queries "show all records" will only list the cities with udated data whereas the in the first case they will at least provide the name of the city with a worrying zero in the "population" field).

Would you go for a cheap but obscure initialization procedure or for more complicated but robust code?

Answer 1

If your database has it, I'd use an upsert (called merge in SQL Server 2008, not sure what other dbs might call it)statement, If not I'd roll my own by updating records that had a matching record and inserting records that do not.

I would not use code I know would fail in predictable circumstances. And in this case, it's not like the code is even complicated. I could write the whole thing in less than ten minutes.