When doing database design, I often use reference/support tables, as we all do. Each time I start up a new project there are inevitably tables which:
- Have a predefined set of values.
- Will never change, and likely never encounter new records (or very infrequently).
A perfect example of this might be a
create table Country ( CountryKey int not null identity ,CountryName varchar(64) not null ,IsoNumber int not null ,Iso3 varchar(8) not null ,constraint pk_country primary key clustered (CountryKey) );
I have used a number of different approaches to "hydrate" these kinds of tables, primarily:
- A hydration SQL script with insert statements.
insert into Country (CountryName, IsoNumber ...) values ('Canada', ...)
A hydration SQL script, reading from disk, using
bulk insert MyDatabase.dbo.Country from ...
And in extreme cases a programmatic script (f#, python).
My preferred approach is the first if all tables only contain a few records. If the tables are beyond this limit, I typically like a script reading from CSV files. I elect to use CSV in that it's both compact and human-readable.
How is everyone else handling this situation?
I realize that this is a somewhat opinionated question. But I figured it was worth asking, since it will inevitably have concrete answers with technical rationale.