if object_id ('raw_cetaf_ids') is not null drop table raw_cetaf_ids

CREATE TABLE raw_cetaf_ids(
	bioRecordKey bigint NOT NULL,
	occurrenceID varchar(512) NOT NULL,
	kingdom varchar(255) NULL
)

bulk insert raw_cetaf_ids from 'f:/tmp/occurrence.txt' --'$(raw_occurrence_file)' --
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar', maxerrors=1000000)

select bioRecordKey, occurrenceID, kingdom,
	case 
		when occurrenceID like '%/herbarium.bgbm.org/object/%' then 'BGBM'
		when occurrenceID like '%/id.luomus.fi/%' then 'Helsinki'
		when occurrenceID like '%/ibot.sav.sk/herbarium/object/%' then 'Bratislava'
		when occurrenceID like '%/coll.mfn-berlin.de/%' then 'MfN'
		when occurrenceID like '%/coldb.mnhn.fr/catalognumber/mnhn/%' then 'Paris' 
		when occurrenceID like '%/data.biodiversitydata.nl/naturalis/specimen/%' then 'Naturalis'
		when occurrenceID like '%/data.nhm.ac.uk/object/%' then 'NHM'
		when occurrenceID like '%/purl.org/nhmuio/id/%' then 'Oslo'
		when occurrenceID like '%/data.rbge.org.uk/herb/%' then 'RBGE'
		when occurrenceID like '%/%.smns-bw.org/%' then 'SMNS'
		when occurrenceID like '%/id.snsb.info/snsb/collection%' then 'SNSB'
		when occurrenceID like '%/id.zfmk.de/collection_ZFMK%' then 'ZFMK'
		when occurrenceID like '%/www.botanicalcollections.be/specimen/%' then 'Meise'
		when occurrenceID like '%/darwinweb.africamuseum.be/object/%' then 'RMCA'
		when occurrenceID like '%/specimens.kew.org/herbarium/%' then 'RBGK'
		when occurrenceID like '%/www.antweb.org/specimen/%' then 'Antweb'
		when occurrenceID like '%/purl.oclc.org/net/edu.harvard.huh/guid/uuid/%' then 'Harvard Herb'
		when occurrenceID like '%/mczbase.mcz.harvard.edu/guid/%' then 'Harvard MCZ'
		when occurrenceID like '%/dnabank.bgbm.org/object/%' then 'GGBN'
		when occurrenceID like '%/%.jacq.org/%' then 'Jacq'
		when occurrenceID like '%/id.herb.oulu.fi/%' then 'OULU'
		when occurrenceID like '%/id.zmuo.oulu.fi/%' then 'ZMUO'
		when occurrenceID like '%/tun.fi/%' then 'OtherFinl'
		when occurrenceID like '%/mus.utu.fi/%' then 'Tartu'
	end as inst
into cetaf_ids
from raw_cetaf_ids

-- alter table cetaf_ids2 add primary key(bioRecordKey)
create clustered index idxInst on cetaf_ids(inst)

-- List of personIDs found in the index
if object_id ('raw_person') is not null drop table raw_person
if object_id ('person') is not null drop table person

create table raw_person(
	personID varchar(512) NOT NULL
)

bulk insert raw_person from 'f:/tmp/person.txt'
with (keepnulls, keepidentity, tablock, DATAFILETYPE = 'widechar', maxerrors=1000)

select distinct personID into person  from raw_person
alter table person add primary key(personID)

