This is the SQL I use (without any of my custom name/location type matching). I use it to generate SQL statements rather than perform the batch update itself as I like to double check the output (and I usually do this on thousands of entries, so running it outside of Recorder means I can still use Recorder!).
select se.survey_event_key as "SEKey",se.Location_name as "CurrentLocationName", LN.item_name as "MatchedLocationName", dbo.LCReturnHectad (UPPER(Se.Spatial_Ref), Se.Spatial_Ref_System) as "Current1kmSquare", dbo.LCReturnHectad (UPPER(l.Spatial_Ref), l.Spatial_Ref_System) as "Matched1kmSquare", Ln.location_key as "MatchedLocationKey",
CASE WHEN (dbo.LCReturnHectad (UPPER(Se.Spatial_Ref), Se.Spatial_Ref_System) = dbo.LCReturnHectad (UPPER(l.Spatial_Ref), l.Spatial_Ref_System)) THEN
'TRUE'
ELSE
'FALSE'
END AS _10kmMatch,
'Update survey_event set location_key = ''' + Ln.location_key + ''' where survey_event_key = ''' + se.survey_event_key + ''';' as "SQL"
from survey_event se
LEFT JOIN Location_Name LN ON location_name = LN.item_name AND LN.Preferred = 1
LEFT JOIN Location L ON LN.Location_Key = L.Location_Key
where survey_key = '%s'
and se.location_key is null
order by l.location_type_key
Then I run
select s.sample_key as "SKey", se.survey_event_key as "SEKey", se.location_key as "SELocationKey",
'Update sample set location_key = ''' + se.location_key + ''' where sample_key = ''' + s.sample_key + ''';' as "SQL"
from sample s
left join survey_event se on se.survey_Event_key = s.survey_event_key
where survey_key = '%s'
and se.location_key is not null
and s.location_key is null
to update the samples to match the survey locations.
Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership