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
Lifesaver!