Editing nonversioned geodatabase data in SQL Server using SQL

You can use SQL to update, insert data into, and delete data from nonversioned tables in the geodatabase if they do not participate in geodatabase behavior. See What type of data can be edited using SQL? for information on the types of data and geodatabase behavior you cannot edit with SQL.

Note:

If the nonversioned table you want to edit using SQL is enabled for archiving, you must edit the table's archive view rather than the table itself. The view will automatically update certain ArcGIS-maintained fields. See What is an archive view and it's related topics for more information.

All data that is registered with the geodatabase has a system-maintained, unique, not-null ObjectID (Row ID) field. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the ObjectID. Execute the Next_RowID procedure to obtain the next available ObjectID value. Then use that value in your INSERT statement to populate the ObjectID field for the record.

Note:

The next available ObjectID is not necessarily the next sequential number after the last inserted ObjectID. ObjectIDs for some client applications are assigned in batches, so the next available ObjectID may be many numbers higher than the last one you used. Also, any unused ObjectIDs from the batch are returned to the pool of available values, which means the next available ObjectID value may actually be a lower number than the one you inserted last.

This set of instructions describes updating one row at a time in a nonversioned table and inserting an ObjectID. You would most likely write a routine or client program to retrieve ObjectIDs and update your data.

1. Log in to an SQL editor such as the one in Microsoft SQL Server Management Studio.

   Be sure to log in to the database as a user who has permission to edit the data.

2. Execute the Next_RowID stored procedure to get a value for the ObjectID field.

   In this example, gisdata4 is the schema where the farmland table is stored.

   DECLARE @id as integer
   EXEC dbo.next_rowid 'gisdata4', 'farmland', @id OUTPUT;
   SELECT @id "Next ObjectID";
   
   Next ObjectID
   423
   

3. Use the value returned from the previous statement to populate the ObjectID field of the record you insert.

   INSERT INTO gisdata4.farmland (objectid,crop,shape)
    VALUES (
     423,
     'oats',
     geography::STGeomFromText('POLYGON((-111.85897004 33.25178949,
   -111.86899617 33.25065270, -111.86887014 33.25062350,
   -111.85884555 33.25176951, -111.85897004 33.25178949))', 4267)
    );
   

4. You can continue editing or, if you are done editing, commit your edits to the database.

If the table also contains a GUID or Global ID field, you must provide a unique value for this field as well. See Inserting a value into a Global ID column in SQL Server using SQL for more information.