|
The technical forum of the NBN Trust for use by the Network. It includes discussions and announcements on the NBN Atlas, Recorder 6 and Indicia |
You are not logged in. Please login or register.
Forum → How Do I... & Other Questions → Cascade record source information
I'm trying to write a batch update to cascade record source information (from events, through to samples then to observations), but having a little trouble with loops... has anyone already written one I can borrow?
Not sure exactly what you are trying to do here. If you could expalin in a bit more details I will try and help.
Mike
What I want to do is run a batch update on a survey event which takes the source (external reference in most cases, though I don't think that matters?) of the survey event and adds it to the samples and observations under the survey event. Clear as mud....?!
Can someone take a look at the following batch update? It's intended to take a CSV containg taxon occurrence keys and source keys and update the TAXON_OCCURRENCE_SOURCES table, but after a minute or two of chugging, nothing (absolutely nothing) happens :/
I've made a lot of assumptions from reading other batch updates, so not convinced I've got everything right - but I'm not getting any errors.
<?xml version="1.0" ?>
<batchupdate menupath="lerc" title="ctoes" description="">
<CSVTables>
<CSVTable name="#CSV1">
<CSVFile description="Taxon Occurrences file" />
<CSVColumn name="RecordKey" datatype="Char" size="16" />
<CSVColumn name="SourceKey" datatype="Char" size="16" />
</CSVTable>
</CSVTables>
<SQL>
<Where keytype="Default">
DECLARE @occurrence_key char(16), @source_key char(16), @source_link_key char(16), @custodian char(8)
DECLARE CSV1_Cursor CURSOR FOR
(SELECT RecordKey, SourceKey FROM #CSV1)
OPEN CSV1_Cursor
FETCH NEXT FROM CSV1_Cursor INTO @occurrence_key, @source_key
-- Loops through the csv data and adds the records
WHILE @@FETCH_STATUS = 0
BEGIN
EXECUTE spNextKey 'TAXON_OCCURRENCE_SOURCES', @source_link_key OUTPUT
INSERT INTO TAXON_OCCURRENCE_SOURCES VALUES(@source_link_key, @occurrence_key, @source_key, '0')
FETCH NEXT FROM CSV1_Cursor INTO @occurrence_key, @souce_key
END
CLOSE CSV1_Cursor
DEALLOCATE CSV1_Cursor
</Where>
</SQL>
</batchupdate>Hmm there was some Recorder black magic going on there - a past error message which hadn't shown up looked like it was interferring - I'm now getting errors - woohoo!!
Bingo - sorted! :D
<?xml version="1.0" ?>
<batchupdate menupath="utilities" title="Create Taxon Occurrence External Sources" description="">
<CSVTables>
<CSVTable name="#CSV1">
<CSVFile description="Taxon Occurrences file" />
<CSVColumn name="RecordKey" datatype="Char" size="16" />
<CSVColumn name="SourceKey" datatype="Char" size="16" />
</CSVTable>
</CSVTables>
<SQL>
<Where keytype="Default">DECLARE @occurrence_key char(16), @source_key char(16), @source_link_key char(16), @custodian char(8)
DECLARE CSV1_Cursor CURSOR FOR
(SELECT RecordKey, SourceKey FROM #CSV1)OPEN CSV1_Cursor
FETCH NEXT FROM CSV1_Cursor INTO @occurrence_key, @source_key
-- Loops through the csv data and adds the records
WHILE @@FETCH_STATUS = 0
BEGIN
SET @custodian = (SELECT Data FROM Setting WHERE Name = 'SiteID')
EXECUTE spNextKey 'TAXON_OCCURRENCE_SOURCES', @source_link_key OUTPUT
INSERT INTO TAXON_OCCURRENCE_SOURCES VALUES(@source_link_key, @occurrence_key, @source_key, '0', @custodian)
FETCH NEXT FROM CSV1_Cursor INTO @occurrence_key, @source_keyEND
CLOSE CSV1_Cursor
DEALLOCATE CSV1_Cursor</Where>
</SQL>
</batchupdate>
Forum → How Do I... & Other Questions → Cascade record source information
Powered by PunBB 1.4.6, supported by Informer Technologies, Inc.