 DECLARE @MaxOrganizationID AS int;
 SELECT @MaxOrganizationID = MAX(OrganizationID) FROM ODM2.ODM2Core.Organizations;
 --Add the analytical labs to the organizations table 
-INSERT INTO ODM2.ODM2Core.Organizations (OrganizationUniqueID, OrganizationUniqueIDTypeCV, OrganizationCode, OrganizationName, 
-	OrganizationDescription, OrganizationTypeCV, OrganizationLink, ParentOrganizationID)
-SELECT DISTINCT NULL AS OrganizationUniqueID, NULL AS OrganizationUniqueIDTypeCV, LabName AS OrganizationCode, LabName AS OrganizationName, 
-	LabOrganization AS OrganizationDescription, 'Analytical Laboratory' AS OrganizationTypeCV, NULL AS OrganizationLink, NULL AS ParentOrganizationID 
+INSERT INTO ODM2.ODM2Core.Organizations (OrganizationTypeCV, OrganizationCode, OrganizationName, OrganizationDescription, OrganizationLink, ParentOrganizationID)
+SELECT DISTINCT 'Analytical Laboratory' AS OrganizationTypeCV, LabName AS OrganizationCode, LabName AS OrganizationName, 
+	LabOrganization AS OrganizationDescription, NULL AS OrganizationLink, NULL AS ParentOrganizationID 
 FROM LittleBearRiverODM.dbo.LabMethods;
 --Create a temporary table that will make this easier
 SELECT DISTINCT lm.LabMethodID + @MaxMethodID + 1 AS MethodID, NULL AS MethodUniqueID, NULL AS MethodUniqueIDTypeCV, lm.LabMethodID + @MaxMethodID + 1 AS MethodCode, 

GO
--Now add the sites 
SET IDENTITY_INSERT ODM2.ODM2Core.SamplingFeatures ON; 
INSERT INTO ODM2.ODM2Core.SamplingFeatures (SamplingFeatureID, SamplingFeatureTypeCV, SamplingFeatureUniqueID, SamplingFeatureUniqueIDTypeCV, SpatialReferenceID, FeatureGeometry)
SELECT s.SiteID AS SamplingFeatureID, 'Site' AS SamplingFeatureTypeCV, NULL AS SamplingFeatureUniqueID, NULL AS SamplingFeatureUniqueIDTypeCV, s.LatLongDatumID AS SpatialReferenceID, 
	geometry::Point(s.Longitude, s.Latitude, sr.SRSID) AS FeatureGeometry
FROM LittleBearRiverODM.dbo.Sites s, LittleBearRiverODM.dbo.SpatialReferences sr
WHERE s.LatLongDatumID = sr.SpatialReferenceID
ORDER BY SamplingFeatureID;

SELECT @MaxSamplingFeatureID = MAX(SamplingFeatureID) FROM ODM2.ODM2Core.SamplingFeatures;
--Create a teporary table with the information that is needed.
SELECT DISTINCT smp.SampleID AS SpecimenID, smp.SampleID + @MaxSamplingFeatureID AS SamplingFeatureID, 'Specimen' AS SamplingFeatureTypeCV, 
	NULL AS SpatialReferenceID, NULL AS FeatureGeometry, smp.LabSampleCode AS SpecimenUniqueID, 'Lab Sample Code' AS SpecimenUniqueIDTypeCV, 
	1 AS IsFieldSpecimen, smp.SampleType AS SpecimenTypeCV, vr.SampleMedium AS SpecimenMediumCV, st.SiteID
INTO #TempSpecimenInfo
FROM LittleBearRiverODM.dbo.Samples smp, LittleBearRiverODM.dbo.DataValues dv, LittleBearRiverODM.dbo.Variables vr, LittleBearRiverODM.dbo.Sites st
WHERE smp.SampleID = dv.SampleID AND dv.VariableID = vr.VariableID AND dv.SiteID = st.SiteID

--Insert the records for the specimens in the ODM2Core.SamplingFeature table using the SampleID + the max SamplingFeatureID as the SamplingFeatureID
SET IDENTITY_INSERT ODM2.ODM2Core.SamplingFeatures ON;
INSERT INTO ODM2.ODM2Core.SamplingFeatures (SamplingFeatureID, SamplingFeatureTypeCV, SpatialReferenceID, FeatureGeometry, SamplingFeatureNote)
SELECT SamplingFeatureID, SamplingFeatureTypeCV, SpatialReferenceID, NULL AS FeatureGeometry, NULL AS SamplingFeatureNote
FROM #TempSpecimenInfo 
ORDER BY SamplingFeatureID;
SET IDENTITY_INSERT ODM2.ODM2Core.SamplingFeatures OFF;
--Insert records into the ODM2SamplingFeatures.Specimens table for the samples
SET IDENTITY_INSERT ODM2.ODM2SamplingFeatures.Specimens ON;
INSERT INTO ODM2.ODM2SamplingFeatures.Specimens (SpecimenID, SamplingFeatureID, IsFieldSpecimen, SpecimenTypeCV, SpecimenMediumCV)
SELECT SpecimenID, SamplingFeatureID, IsFieldSpecimen, SpecimenTypeCV, SpecimenMediumCV
FROM #TempSpecimenInfo
ORDER BY SpecimenID;
SET IDENTITY_INSERT ODM2.ODM2SamplingFeatures.Specimens OFF;
