Adding New records

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.

<?xml version="1.0" ?>
<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.

</CSVTable>
</CSVTables>

<SQL>

This example works only from an existing event so all the code is in <Where keytype="Event"> .

<Where keytype="Default">
SELECT 'Survey Event Driven' as Answer From setting
</Where>

Variables are defined to hold all the data. for output.

DECLARE @Count INT,
@Sample_Key CHAR(16),
@Vague_Date_Start int,
@Vague_Date_End int,
@Vague_Date_Type varchar(2),
@Spatial_Ref varchar(20),
@Spatial_ref_System varchar(4),
@Lat Float,
@Long Float,
@Outstanding_Card int,
@Spatial_Ref_Qualifier varchar(20),
@Sample_type_Key char(16),
@Location_Key char(16),
@Survey_Event_Key Char(16),
@Entered_By char(16),
@Entry_Date DateTime,
@Location_Name Varchar(100),
@data varchar(20),
@Accuracy varchaR(20),
@Measurement_Qualifier_key char(16),
@Measurement_Unit_key char(16),
@SamplE_data_key char(16),
@Custodian char(8)

Current date/time is obtained

SET @Entry_date= GETDATE()

Name key for the users currently logged on is obtained (new 6.14 feature)

SET @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

SET @Sample_Type_key = 'NBNSYS0000000001'

The Survey_Event_Key currently highlighted in the observation hierarchy is allocated to a variable.

SET @Survey_event_key = '%s'

set @Accuracy = 'Accurate'
set @Measurement_Qualifier_key = 'NBNSYS0000000001'
set @Measurement_Unit_key = 'NBNSYS0000000001'

Data from the Survey_Event is transferred into the variables

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
   FROM #CSV1
  )

Open the CURSOR

OPEN CSV1_Cursor

Fetch the first record.

FETCH NEXT FROM CSV1_Cursor
INTO @spatial_ref,@Location_name,@data

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
  BEGIN

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.

    INSERT INTO Sample(
              Sample_key,
      Vague_date_Start,
                                                Vague_date_end,
      Vague_Date_Type,
      Spatial_Ref,
      Spatial_Ref_System,
      Lat,
                                                Long,
      Outstanding_Card,
      Spatial_ref_Qualifier,
      Sample_Type_Key,
      Location_key,
      Survey_Event_key,
      Entered_By,
      Entry_date,
      Location_Name,
      Custodian )
    VALUES (@Sample_Key,
                                            @Vague_date_start,
         @Vague_date_end,
         @Vague_Date_Type,
         @Spatial_ref,
                @Spatial_Ref_System,
                                            @Lat,
                                            @Long,
         @Outstanding_Card,
                                            @spatial_ref_Qualifier,
         @Sample_type_Key,
                                            @Location_Key,
         @Survey_Event_key,
         @Entered_BY,
         @Entry_date ,
         @Location_Name,
         @Custodian )

.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.

INSERT INTO SAMPLE_RECORDER (Sample_key,SE_Recorder_Key,Entered_By,Entry_date)
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
                BEGIN
                       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)
                END

   The next record is fetched.

   FETCH NEXT FROM CSV1_Cursor
            INTO @spatial_ref,@Location_name,@data


  END

The Cursor is closed and deallocated

CLOSE CSV1_Cursor
DEALLOCATE CsV1_Cursor

SELECT @Count AS COUNT

</Where>

</SQL>
</batchupdate>

Relate Topics:
  Condition
  Columns
  XML Batch Updates Help
  Introduction
  About XML Batch Updates
  Notes