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.

All data that is registered with the geodatabase has a system-maintained, unique, not-null object ID (row ID) column. When you use SQL to insert records into nonversioned tables in the geodatabase, you must provide a unique value for the object ID.

To get this information, query the sde_table_registry ArcSDE system table to get the registration ID of the table in which you want to insert a record. Once you have the registration ID, you can identify the table's corresponding i table, because i tables are associated with their business table through the registration ID. The i table is stored in the schema of the user who owns the corresponding table.

Execute the i<registration_id>_get_id procedure to obtain the next available object ID value. Then use that value in your INSERT statement to populate the object ID column for the record.

Steps:

1. Log in to a SQL editor such as the SQL editor 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. Query the SDE_table_registry system table to determine the registration ID of the business table to which you want to insert a record.

   In this example, the business table is farmland and the owner of the table is gisdata4.

   SELECT registration_id
   FROM dbo.SDE_table_registry
   WHERE table_name = 'farmland' and owner = 'gisdata4';
   
   registration_id
         71

   The registration ID for the gisdata4.farmland business table is 71.

3. Use the i71_get_id procedure to get a value for the object ID column.

   DECLARE @id as integer
   DECLARE @num_ids as integer
   EXEC dbo.i71_get_ids 2, 1, @id output, @num_ids output; 

   The 2 after the procedure is a constant that indicates you want to return an object ID. Do not use any other value in this place. The 1 indicates you want one object ID returned from the i71 table. Since you are updating just one record, you only need one object ID.

4. Use the value returned from the previous statement to populate the object ID column of the record you insert.

   In this example, fid is the object ID column.

   INSERT INTO gisdata4.farmland (fid,crop,shape)
   VALUES (
   @id,
   '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));