-- First, count the taxa and update the counts in table "taxon"
select bioRecordTaxonKey as tKey, count(*) as uCount
into #tmpTaxa
from bioRecord_base
group by bioRecordTaxonKey;

update taxon
set unitCount = IsNull(uCount, 0)
from taxon
left join #tmpTaxa on tKey = taxonKey;

-- then, create all the suggest tables
select taxonName as Name, sum(unitCount) as unitCount
into suggestTaxon
from taxon
group by taxonName;

select genus as Name, sum(unitCount) as unitCount
into suggestGenus
from taxon
group by genus;

select family as Name, sum(unitCount) as unitCount
into suggestFamily
from taxon
group by family;

select higherTaxonName as Name, sum(unitCount) as unitCount
into suggestHigherTaxon
from higherTaxon
join taxon on taxon.taxonKey = higherTaxon.taxonKey
group by higherTaxonName;

select vernacularName as Name, sum(unitCount) as unitCount
into suggestVernacular
from vernacularName
join taxon on vernacularTaxonKey = taxonKey
group by vernacularName;

select countryName as Name, count(*) as unitCount
into suggestCountry
from bioRecord_base
join isoCountry on bioRecordCountryCode = isoCountryCode
group by countryName;

select institution_code as Name, count(*) as unitCount
into suggestInstCode
from bioRecord_base
join institution_code on id = institution_code_id
group by institution_code;

select collection_code as Name, count(*) as unitCount
into suggestCollCode
from bioRecord_base
join collection_code on id = collection_code_id
group by collection_code;

-- create indexes
create clustered index idxInstCode on suggestInstCode(Name);
create clustered index idxCollCode on suggestCollCode(Name);
create clustered index idxCountry on suggestCountry(Name);
create clustered index idxVernacular on suggestVernacular(Name);
create clustered index idxHigherTaxon on suggestHigherTaxon(Name);
create clustered index idxTaxon on suggestTaxon(Name);
create clustered index idxFamily on suggestFamily(Name);
create clustered index idxGenus on suggestGenus(Name);

drop table #tmpTaxa;