HOW TO
In some cases, there may be a need to insert geometries into a feature class using structured query language (SQL). This can be done using a loop or a database cursor in SQL. Sometimes, due to performance, resources, or workflows, creating data via SQL is preferable to creating the data in ArcMap.
-- Get the number of rows in the looping table DECLARE @RowCount INT SET @RowCount = (SELECT COUNT(*) FROM sde.INPUT_TABLE) -- Declare an iterator DECLARE @iterator INT -- Initialize the iterator SELECT @iterator = MIN(OBJECTID) FROM SDE.INPUT_TABLE -- Loop through the rows of a table @myTable WHILE @iterator is NOT NULL BEGIN DECLARE @id as integer EXEC sde.next_rowid 'sde', 'points', @id OUTPUT; INSERT INTO sde.POINTS (OBJECTID, Shape) SELECT @id, geometry::STPointFromText('POINT('+str(POINT_X, 38, 8) + ' ' + str(POINT_Y, 38, 8) + ')', 2286) as SHAPE FROM sde.INPUT_TABLE where objectid = @iterator; SELECT @iterator= MIN(OBJECTID) FROM SDE.INPUT_TABLE WHERE @iterator < @RowCount END
In the example below, point geometry is inserted into a feature class from an existing database table.
Note: This procedure works best if a unique identifier column exists in the input database table, such as a rownum or OID field in SQL Server. This ensures the WHILE loop does not miss any rows that are not unique.
Note: To insert data into a registered feature class, a unique, not-null value must be specified for the ObjectID column. To do this, the Next_RowID procedure must be used.SQL Server Geometry sample:
-- Get the number of rows in the looping table DECLARE @RowCount INT SET @RowCount = (SELECT COUNT(*)FROM sde.INPUT_TABLE) -- Declare an iterator DECLARE @iterator INT -- Initialize the iterator SELECT @iterator = MIN(OBJECTID) FROM SDE.INPUT_TABLE -- Loop through the rows of a table WHILE @iterator is NOT NULL BEGIN DECLARE @id as integer EXEC sde.next_rowid 'sde', 'points', @id OUTPUT; INSERT INTO sde.POINTS (OBJECTID, Shape) SELECT @id, geometry::STPointFromText( 'POINT('+str(POINT_X, 38, 8) + ' ' + str(POINT_Y, 38, 8) + ')', 2286) as SHAPE FROM sde.INPUT_TABLE where objectid = @iterator; SELECT @iterator= MIN(OBJECTID) FROM SDE.INPUT_TABLE WHERE @iterator < @RowCount ENDOracle SDO Geometry sample:
SET SERVEROUTPUT ON -- Declare a cursor DECLARE CURSOR points is select * from sde.input_table; --Loop through the cursor BEGIN FOR pt IN points LOOP INSERT INTO SDE.POINTS (OBJECTID, SHAPE) VALUES ( sde.gdb_util.next_rowid('sde', 'points'), MDSYS.SDO_GEOMETRY(2001,2286,MDSYS.SDO_POINT_TYPE(pt.point_x, pt.point_y,NULL),NULL,NULL) ); END LOOP; COMMIT; END; /Oracle ST Geometry sample:
SET SERVEROUTPUT ON -- Declare a cursor DECLARE CURSOR points is select * from sde.input_table; --Loop through the cursor BEGIN FOR pt IN points LOOP INSERT INTO SDE.POINTS (OBJECTID, SHAPE) VALUES ( sde.gdb_util.next_rowid('sde', 'points'), sde.ST_Point(pt.point_x,pt.point_y, 2286) ); END LOOP; COMMIT; END; /
Get help from ArcGIS experts
Download the Esri Support App