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 name="#CSV1">
          <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"> .

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

<Where keytype="Event">

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

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

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

  (   SELECT Spatial_ref,Location_name,Sample_data

Open the CURSOR

OPEN CSV1_Cursor
Fetch the first record.

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

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(
      Custodian )
    VALUES (@Sample_Key,
         @Entry_date ,  
         @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
                       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 fetched.
             INTO @spatial_ref,@Location_name,@data

  The Cursor is closed and deallocated

  CLOSE  CSV1_Cursor






Relate Topics:
  XML Batch Updates Help
  About XML Batch Updates
Created by Atop CHM to web converter,© 2009 all right reserved.