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

select bioRecordKey, 
	Iif(r.institution_code is NULL, -1, ic.id) as institution_code_id, 
	Iif(r.collection_code is NULL, -1, cc.id) as collection_code_id,
	bioRecordTaxonKey, resourceKey as bioRecordResourceKey, bioRecordCountryCode,
	gatheringYear, gatheringDate, recordBasis,
	bioRecordId, geoLongitude, geoLatitude,	collectorNumber, fieldNumber, collector, localityText
	--NULL as accessionNumber, NULL as guid
into bioRecord
from syn_raw.dbo.raw_occurrence r
left join syn_raw.dbo.raw_dataset on dataset_uuid = datasetID
left join syn_raw.dbo.institution_code ic on r.institution_code = ic.institution_code
left join syn_raw.dbo.collection_code cc on r.collection_code = cc.collection_code
