 
 --Populate the ODM2.SpatialReferences table with the SpatialReferences that 
 --were used in the ODM 1.1.1 database
+--TODO:  If we eventually provide a list of spatial references for ODM2, this
+--part of the code will have to be fixed to match spatial references by name
 SET IDENTITY_INSERT ODM2.ODM2.SpatialReferences ON;
 INSERT INTO ODM2.ODM2.SpatialReferences (SpatialReferenceID, SRSCode, SRSName, 
 	SRSDescription, SRSLink)

WHERE LBRUnits.UnitsName = ODM2Units.UnitsName;

-------------------------------------------------------------------------------
--Populate the ODM2.ProcessingLevels table
--NOTE:
--1.  Equivalent to QualityControlLevels in ODM 1.1.1 and so moved across and
--    uses the existing IDs
-------------------------------------------------------------------------------
--Load the QualityControlLevels from ODM 1.1.1
SET IDENTITY_INSERT ODM2.ODM2.ProcessingLevels ON; 
INSERT INTO ODM2.ODM2.ProcessingLevels (ProcessingLevelID, ProcessingLevelCode, 
	Definition, Explanation)

ORDER BY ResultID, ValueDateTime;
SET IDENTITY_INSERT ODM2.ODM2.TimeSeriesResultValues OFF;

--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--Populate the ODM2.SamplingFeatures, ODM2.Specimens, ODM2.RelatedFeatures, 
--ODM2.Actions, ODM2.ActionBy, ODM2.FeatureActions, ODM2.RelatedActions, 
--ODM2.Results, ODM2.MeasurementResults, and ODM2.MeasurementResultValues 
--tables for specimen-based Results
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

-------------------------------------------------------------------------------
--Load the specimens from the ODM 1.1.1 Samples table into the ODM2 
--SamplingFeatures table and return a temp table that contains the 
--SamplingFeatureIDs linked with the SamplingFeatureCodes
-------------------------------------------------------------------------------
CREATE TABLE #tempInsertedSpecimens
(
	SamplingFeatureID INT,
	SamplingFeatureCode VARCHAR(200) NOT NULL
);
INSERT INTO ODM2.ODM2.SamplingFeatures (SamplingFeatureTypeCV, 
	SamplingFeatureCode, SamplingFeatureName, SamplingFeatureDescription,
	SamplingFeatureGeoTypeCV, FeatureGeometry, Elevation_m, ElevationDatumCV)
OUTPUT INSERTED.SamplingFeatureID, INSERTED.SamplingFeatureCode 
INTO #tempInsertedSpecimens
SELECT DISTINCT 'Specimen' AS SamplingFeatureTypeCV, 
	LabSampleCode AS SamplingFeatureCode, NULL AS SamplingFeatureName, 
	'Specimen loaded from an ODM 1.1.1 database.' AS SamplingFeatureDescription,
	'Not applicable' AS SamplingFeatureGeoTypeCV, NULL AS FeatureGeometry, 
	NULL AS Elevation_m, NULL AS ElevationDatumCV
FROM LittleBearRiverODM.dbo.Samples;

-------------------------------------------------------------------------------
--Load the specimens from the ODM 1.1.1 Samples table into the ODM2
--Specimens table
--NOTES:
--1.  Before running this code, need to make sure that the SampleType terms in
--    the Samples table of the ODM 1.1.1 database match valid ODM2 
