AnsweredAssumed Answered

Bulk Insert Head Scratcher

Question asked by tpcolson Champion on Sep 26, 2019
Latest reply on Sep 26, 2019 by Christian_Wells-esristaff

So I'm following Iterating insert geometry statements via T-SQL  which I have used many many many many mnay many many times in the past to do bulk inserts from non-SDE sources into a SDE feature class. Now I'm on SDE 10.7.x and using the following: 

 

DECLARE @RowCount INT  
SET @RowCount = (SELECT COUNT(*) FROM [db1].[dbo].source_table WHERE FCSUBTYPE = 22 OR FCSUBTYPE = 23 OR FCSUBTYPE = 24  )
DECLARE @iterator INT 
SELECT @iterator = MIN(OBJECTID) FROM [db1].[dbo].source_table  WHERE FCSUBTYPE = 22 OR FCSUBTYPE = 23 OR FCSUBTYPE = 24
WHILE @iterator is NOT NULL
BEGIN 
  DECLARE @id as integer 
  EXEC dbo.next_rowid 'dbo', 'dest_table', @id OUTPUT;
  INSERT INTO [db2].[dbo].[dest_table]([OBJECTID],[CREATEDATE],[EDITDATE],[MAPMETHOD],[MAPSOURCE],[SOURCEDATE],[XYACCURACY],[NOTES],[GlobalID],[COUNTY],[MANAGEMENTZONE],[QUADNAME],
  [ROAD],[PARKDISTRICT],[STATE],[WATERSHED],[TRAIL],[X_COORD],[Y_COORD],[LAT],[LON],[ELEVATION],[LOC_NAME],[FC_SUBTYPE],[LOCATION_DESCRIPTION],[Shape],[STREAMNAME],[YEAR],[VALID_RESULT],
  [PUBLICDISPLAY],[DATAACCESS],[UNITCODE],[UNITNAME],[REGIONCODE],[CREATEUSER],[EDITUSER])


  SELECT @id, [CREATE_DATE],[EDITDATE],CASE WHEN [MAPMETHOD] ='HDIG' THEN 'Digitized' WHEN [MAPMETHOD] ='UNKN' THEN 'Unknown' WHEN [MAPMETHOD] ='AGPS' THEN 'Autonomous GPS' WHEN [MAPMETHOD] ='DERV' THEN 'Feature Extraction' WHEN [MAPMETHOD] ='' THEN 'Unknown' WHEN [MAPMETHOD] IS NULL THEN 'Unknown' END AS [MAPMETHOD],
  [MAPSOURCE], [SOURCEDATE],CASE WHEN [HERROR] = '>10m <=100m' THEN '>=14m' WHEN [HERROR] = '>1m <=5m' THEN '>=1m and <5m' WHEN [HERROR] = '>5000m' THEN 'Scaled' WHEN [HERROR] = '>5m <=10m' THEN '>=5m and <14m' WHEN [HERROR] = 'Unknown' THEN 'Unknown' WHEN [HERROR] = '' THEN 'Unknown' WHEN [HERROR] IS NULL THEN 'Unknown'END AS [XYACCURACY],
  [NOTES],[GIS_LOCATION_ID],[COUNTY],[MANAGEMENTZONE],[QUADNAME],[ROAD],[PARKDISTRICT],[STATE],[WATERSHED],[TRAIL],[X_COORD],[Y_COORD],[LAT],[LON],[ELEVATION],[LOC_NAME],[FCSUBTYPE],[LOCATIONDESCRIPTION],
  [SHAPE],[STREAMNAME],[YEAR], CASE WHEN [VALID] = 'Certified' THEN 'Certified' WHEN [VALID] = '' THEN 'Raw' WHEN [VALID] IS NULL THEN 'Raw' END AS [VALID_RESULT],'No Public Map Display' AS [PUBLICDISPLAY],
  'Secure Access Only' AS [DATAACCESS], 'GRSM' AS [UNITCODE], 'Great Smoky Mountains National Park' AS [UNITNAME], 'SER' AS [REGIONCODE], 'GRSM User' AS [CREATEUSER], 'GRSM User' AS [EDITUSER]
  FROM [db1].[dbo].source_table WHERE FCSUBTYPE = 22 OR FCSUBTYPE = 23 OR FCSUBTYPE = 24 AND objectid = @iterator;
  SELECT @iterator= MIN(OBJECTID) FROM [db1].[dbo].source_table  WHERE @iterator < OBJECTID AND  FCSUBTYPE = 22 OR FCSUBTYPE = 23 OR FCSUBTYPE = 24
END

 

Which results in the whole thing looping as many times as there are objects meeting the where clause. E.g it's selecting all 566 rows, 566 times. 

 

Christian Wells This leads me to believe that how a new OID is generated has changed since that 2014 blog post? 

 

 

I save all my SQL code, so I opened up the last one I ran successfully on a 10.5.x SDE, and yup, no change other than the where clauses (which I removed to see if that was the cause, which they are not). 

Outcomes