Mending bad BAD database design once data is in the system

Question

I know that that is not a question... erm anyway HERE is the question.

I have inherited a database that has 1(one) table in that looks much like this. Its aim is to record what species are found in the various (200 odd) countries.

ID 
Species
Afghanistan
Albania
Algeria
American Samoa
Andorra
Angola
....
Western Sahara
Yemen
Zambia
Zimbabwe

A sample of the data would be something like this

id Species Afghanistan Albania American Samoa
1  SP1         null     null        null
2  SP2          1         1         null
3  SP3         null      null         1

It seems to me this is a typical many to many situation and I want 3 tables. Species, Country, and SpeciesFoundInCountry

The link table (SpeciesFoundInCountry) would have foreign keys in both the species and Country tables.

(It is hard to draw the diagram!)

Species
SpeciesID  SpeciesName

Country
CountryID CountryName

SpeciesFoundInCountry
CountryID SpeciesID

Is there a magic way I can generate an insert statement that will get the CountryID from the new Country table based on the column name and the SpeciesID where there is a 1 in the original mega table?

I can do it for one Country (this is a select to show what I want out)

SELECT Species.ID, Country.CountryID
FROM Country, Species
WHERE (((Species.Afghanistan)=1)) AND (((Country.Country)="Afghanistan"));

(the mega table is called species)

But using this strategy I would need to do the query for each column in the original table.

Is there a way of doing this in sql?

I guess I can OR a load of my where clauses together and write a script to make the sql, seems inelegant though!

Any thoughts (or clarification required)?

Answer 1

I would use a script to generate all the individual queries, since this is a one-off import process.

Some programs such as Excel are good at mixing different dimensions of data (comparing column names to data inside rows) but relational databases rarely are.

However, you might find that some systems (such as Microsoft Access, surprisingly) have convenient tools which you can use to normalise the data. Personally I'd find it quicker to write the script but your relative skills with Access and scripting might be different to mine.

Answer 2

Why do you want to do it in SQL? Just write a little script that does the conversion.