Christian_Wells-esristaff

Iterating insert geometry statements via T-SQL

Blog Post created by Christian_Wells-esristaff Employee on Oct 1, 2014

For some users, it is much more efficient to insert data into their SDE feature classes via SQL statements. Although its fairly straightforward to do this for a single feature, it gets a little more complicated when a lot of records need to be inserted while referencing the sde.next_rowid function. However, one way to fulfill the constraints of the primary key and use proper sequencing is to create an iterator in SQL Server. The below example shows how this can be done:

 

-- Truncate the table to have the records inserted
TRUNCATE TABLE sde.pipeline_points

-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(*) FROM [sde].[INPUTTABLE]) 

-- Declare an iterator
DECLARE @iterator INT
-- Initialize the iterator
SELECT @iterator = MIN(OBJECTID) FROM SDE.INPUTTABLE

-- 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', 'pipeline_points', @id OUTPUT;
  -- Insert the data while casting the points geometry 
  INSERT INTO sde.pipeline_points
  SELECT
  @id, 
  LATITUDE, 
  LONGITUDE, 
  geometry::STPointFromText('POINT('+str(LONGITUDE, 20, 10) + ' ' + str(LATITUDE, 20, 10) + ')', 4326) as SHAPE
  FROM [sde].[INPUTTABLE] where objectid = @iterator;
  
  -- Update the iterator value
  SELECT @Iiterator= MIN(OBJECTID) FROM SDE.INPUTTABLE WHERE @iterator < OBJECTID
END

 

 

This script is also helpful for selecting geometries on the fly from the Lat/Long fields within a table without having to use the Display XY tool in ArcMap.

 

SELECT *, 
       geometry::STPointFromText('POINT(' + Str(LONGITUDE, 20, 10) + ' ' + Str(LATITUDE, 20, 10) + ')', 4326) AS shape 
FROM  database.SCHEMA.table

Outcomes