Subscribe Now: Feed Icon

Wednesday, March 27, 2013

SDE: Using SQL to copy feature rows

(Or to Bulk Insert features to a feature table)

This only works if you are using Oracle/MS SQL geometry types (where the shape field is not an integer but an actual shape).

I always thought that for copying more than one row of feature data I will need to use ArcCatalog, for one row I usually hard coded the OBJECTID field and hoped that the next person using ArcCatalog won’t hate my guts…

Well last week I needed to duplicate rows in a feature table and found this link, and even though it says ArcSDE 9.3.1 as the latest version it works on version 10.

Use:

SELECT table_name, registration_id FROM sde.table_registry WHERE owner = 'OWNER'

To find the registration number of the feature table, for example 21.

 

To find the next available ObjectId:

sde.version_user_ddl.next_row_id('OWNER', 21)

 

To copy the rows of Table_Name (for example: from type 1 to type 2):

insert into Table_Name(objectid, shape, Type) select sde.version_user_ddl.next_row_id('OWNER', 21), shape, 2  from Table_Name t where Type = 1

 

And that's it.