 --2.  This uses "Research Institute" as the OrganizationTypeCV for now - this won't be true for all organizations stored in ODM 1.1 databases
 --------------------------------------------------------------------------------------
 SET IDENTITY_INSERT ODM2.ODM2Core.Organizations ON; 
-INSERT INTO ODM2.ODM2Core.Organizations (OrganizationID, OrganizationUniqueID, OrganizationUniqueIDTypeCV, OrganizationCode, 
-	OrganizationName, OrganizationDescription, OrganizationTypeCV, OrganizationLink, ParentOrganizationID)
-SELECT SourceID AS OrganizationID, NULL AS OrganizationUniqueID, NULL AS OrganizationUniqueIDTypeCV, SourceID AS OrganizationCode, 
-	Organization AS OrganizationName, CAST(SourceDescription AS VARCHAR(500)) AS OrganizationDescription, 'Research Institute' AS OrganizationTypeCV, 
+INSERT INTO ODM2.ODM2Core.Organizations (OrganizationID, OrganizationTypeCV, OrganizationCode, 
+	OrganizationName, OrganizationDescription, OrganizationLink, ParentOrganizationID)
+SELECT SourceID AS OrganizationID, 'Research Institute' AS OrganizationTypeCV, SourceID AS OrganizationCode, 
+	Organization AS OrganizationName, CAST(SourceDescription AS VARCHAR(500)) AS OrganizationDescription,  
 	SourceLink AS OrganizationLink, NULL AS ParentOrganizationID 
 FROM LittleBearRiverODM.dbo.Sources 
 ORDER BY OrganizationID;

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 
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, SpatialReferenceID, FeatureGeometry, SamplingFeatureNote)
SELECT s.SiteID AS SamplingFeatureID, 'Site' AS SamplingFeatureTypeCV, s.LatLongDatumID AS SpatialReferenceID, 
	geometry::Point(s.Longitude, s.Latitude, sr.SRSID) AS FeatureGeometry, s.Comments AS SamplingFeatureNote
FROM LittleBearRiverODM.dbo.Sites s, LittleBearRiverODM.dbo.SpatialReferences sr
WHERE s.LatLongDatumID = sr.SpatialReferenceID
ORDER BY SamplingFeatureID;
