Bulk Insert Head Scratcher

740
3
09-26-2019 10:01 AM
ThomasColson
MVP Frequent Contributor

So I'm following https://community.esri.com/people/Christian_Wells-esristaff/blog/2014/10/01/iterating-insert-geometr...  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). 

0 Kudos
3 Replies
ChristianWells
Esri Regular Contributor

Thanks for sharing your findings. Let me take a look at 10.7 and get back to you. What version of SQL Server are you using?

0 Kudos
ThomasColson
MVP Frequent Contributor

10.7.1.2.4, SQL 2014 ENT, Storage Type Geography. What's different from your post is, my source and destination tables are in different database........

0 Kudos
ChristianWells
Esri Regular Contributor

Here is my testing:

  1. Create db1 as an Enterprise Geodatabase (same instance)
  2. Create db2 as a standalone database (same instance)
  3. Create a spatial table in db2 with 100 records using Create Random Points
    1. Fields (OID, Shape)
  4. Create an empty feature class in db1 with the same schema
    1. Fields (OID, Shape)
  5. Run the following code:
    -- Get the number of rows in the looping table  
    DECLARE @RowCount INT  
    SET @RowCount = (SELECT COUNT(*) FROM [db1].[dbo].[ORIGINFC])   
      
    -- Declare an iterator  
    DECLARE @iterator INT  
    -- Initialize the iterator  
    SELECT @iterator = MIN(OID) FROM db1.dbo.ORIGINFC 
      
    -- Loop through the rows of a table  
    WHILE @iterator is NOT NULL  
      
    BEGIN  
      DECLARE @id as integer  
      -- Get OBJECTID value  
      EXEC sde.next_rowid 'sde', 'destinationfc', @id OUTPUT;
      -- Insert the data while casting the points geometry
      INSERT INTO SDE.destinationfc (oid, shape) SELECT @id,Shape FROM db1.dbo.ORIGINFC where OID = @iterator;      
      -- Update the iterator value
      SELECT @iterator= MIN(OID) FROM db1.dbo.ORIGINFC WHERE @iterator < OID
    END‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍
  6. Query the destination table count which should equal 100

I ran this with a 10.7.1 EGDB and SQL Server 2014 (12.0.6108.1) but I don't see duplicate records in the destination feature class. 

Could you repeat my steps with a generic feature class and share the results you receive? I know this is a really simplified example but maybe it will point us towards the cause. 

0 Kudos