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
