Adding New records
XML Batch updates can be used to add new records to the database. This feature shoould be used with care as there will be no validation checks on the data added. Please make sure you understand the data structure and the validation requirements before attempting to add data in this way. Batch updates do little validation and the update will probably fail completely if there are errors in the data.
The following is an annotated example (click here for a copy without the annotation) of an update which adds Samples to an existing Survey Events. It uses a new User Defined Function to calulate lat/longs, which is available at LLCGRTOLLGBNI. Something similar to the following could be developed to load Location from a csv file.
<batchupdate menupath="LcBatchUpdates" title="LCBU7 - CreateSamples - creates new Sample"
description="Creates new Sample and Sample_Data and Sample Recorder entries for a Survey Event based on grid refs and sample data in a CSV file. If run from Survey Event only.
Dates are taken from the Survey_Event_Date. Column names required are Spatial_ref,Location_name,Sample_data. Location Name is optional." >
This defines the layout of the csv files. Columns not defined are ignored.
<CSVFile description="Sample Import Data" />
<CSVColumn name = "Spatial_ref" datatype="varChar" size="20" />
<CSVColumn name = "Location_name" datatype="varChar" size="100" />
<CSVColumn name = "Sample_Data" datatype="varChar" size="20" />
This example works only from an existing event so all the code is in <Where keytype="Event"> .
SELECT 'Survey Event Driven' as Answer From setting
Variables are defined to hold all the data. for output.
DECLARE @Count INT,
Current date/time is obtained
SET @Entry_date= GETDATE()
Name key for the users currently logged on is obtained (new 6.14 feature)
@Entered_By = (SELECT NAME_KEY FROM NAME WHERE
<Condition field="Name.Name_Key" type="CurrentUserID" name="Current User ID" operator="equal" /> )
Constants are defined.
SET @Spatial_Ref_System = 'OSGB'
SET @Spatial_Ref_Qualifier = 'Estimated from map'
SET @outstanding_Card = 0
@Sample_Type_key = 'NBNSYS0000000001'
The Survey_Event_Key currently highlighted in the observation hierarchy is allocated to a variable.
SET @Survey_event_key = '%s'
@Accuracy = 'Accurate'
set @Measurement_Qualifier_key = 'NBNSYS0000000001'
set @Measurement_Unit_key = 'NBNSYS0000000001'
Data from the Survey_Event is transferred into the
set @vague_date_type = (SELECT Vague_date_type from Survey_event SE where Survey_Event_key = @Survey_event_key)
set @vague_date_start= (SELECT Vague_date_start from Survey_event SE where Survey_Event_key = @Survey_event_key)
set @vague_date_end= (SELECT Vague_date_end from Survey_event SE where Survey_Event_key = @Survey_event_key)
The Custodian is obtained form the Setting table. Note [NAME] is in square brackets as it is a reserved word.
set @Custodian = (SELECT DATA FROM SETTING WHERE [NAME] = 'Site_Id')
A CURSOR (pointer to a current object) is used to loop through the entries in the CSV file.
Declare the Cursor .
Include the fields which need to be extracted.
DECLARE CSV1_Cursor CURSOR FOR
( SELECT Spatial_ref,Location_name,Sample_data
Open the CURSOR
Fetch the first record.
Loop through the records and get each of the records in turn. @@FETCH is a system function which returns 0 until all the records have been read.
-- Loops through the csv data and adds the records
WHILE @@FETCH_STATUS = 0
A stored procedure is used to get the next key for the sample table.
EXECUTE spNextKey 'Sample', @Sample_key OUTPUT
The new UDF is used to
get the Lat/Long from the spatial reference
set @Lat = dbo.LCGRTOLLGNI(@Spatial_ref,'OSGB',1,0)
set @Long = dbo.LCGRTOLLGBNI(@Spatial_ref,'OSGB',0,0)
SET @count = @count + 1
A standard INSERT statement is used to insert the variable into the Sample table.
.An insert statement is used to populate Sample_Recorder. This is an unusual table which doesn't have a unique key.
In this case all Recorders for the event are added to the sample.
SELECT @Sample_Key,SER.SE_Recorder_Key,@entered_By,@Entry_date FROM
Survey_event_recorder SER where SER.Survey_event_key = @Survey_Event_key
A check is made to see if there is any data and if so the next Sample_Data_Key is obtained and the record added.
if len(@data) > 0
EXECUTE spNextKey 'Sample_data', @Sample_Data_key OUTPUT
INSERT INTO SAMPLE_DATA (SamplE_data_Key,Sample_key,Data,Measurement_qualifier_key,Measurement_Unit_key,Entered_By,Entry_date)
VALUES (@SamplE_data_key, @Sample_Key,@Data,@Measurement_Qualifier_key,@Measurement_Unit_Key,@entered_by,@entry_date)
The next record is
FETCH NEXT FROM CSV1_Cursor
The Cursor is closed and deallocated
SELECT @Count AS COUNT