How can I insert new records to an ArcSDE feature class from a view in ArcSDE

Question

I am wondering how I can both insert new records from a view to a feature class and update data that may be newer in the view than in the feature class. I have been fumbling around in modelbuilder and arcpy and haven't made any significant headway yet. I need to be able to export the model or script so that it can be run as a recurring job.

Thanks!

Answer 1

Not sure if there's a way to do this by inserting/updating records into the feature class (probably requires the SDE C or Java API), but if it's acceptable to truncate the feature class and re-create the features by copying from the non-spatial view, then you can do so either in ModelBuilder or arcpy by chaining together the 'Delete Features', 'Make XY Event Layer' and 'Feature Class to Feature Class' tools (all in the Data Management toolbox). If doing so in python, make sure the gp.overwriteoutput = true (or arcpy equivalent) or it will fail since the feature class already exists. ModelBuilder will give a warning but will still complete.

A couple of recommendations when doing this: 1. Get it running in ModelBuilder first and then export to python 2. Do NOT use 'CopyFeatures_Management', as that takes many many times longer in python than it does in ModelBuilder (depending on the number of features of course, but it is extremely slow and does not scale well at all).

Here's an excerpt of the AGS v9.3.1/python v2.5 version that reprojects the feature class after creating a temp feature class:

# Import system modules
import sys, string, os, arcgisscripting

# Create the Geoprocessor object
gp = arcgisscripting.create()

installInfo = gp.GetInstallInfo("server .net")
ARCGIS_INSTALL_DIR = installInfo["InstallDir"]

# Load required toolboxes...
gp.AddToolbox(ARCGIS_INSTALL_DIR + "/ArcToolbox/Toolboxes/Data Management Tools.tbx")
gp.AddToolbox(ARCGIS_INSTALL_DIR + "/ArcToolbox/Toolboxes/Conversion Tools.tbx")
gp.overwriteoutput = 1

# Local variables...
sdeConn = "Database Connections/myDbConn.sde"
inMemXYLyr = "sde.dbo.Temp_Layer"
XYTblOrView = sdeConn + "/sde.dbo.TableName" # assuming table is in SDE DB; your mileage may vary
tmpFc = "TEMP_FEATURE_CLASS"
tmpFcFull = sdeConn + "/sde.DBO." + TmpFc
targetFc = sdeConn + "/sde.DBO.MY_FEATURE_CLASS"

# Delete the features first
print "Deleting features in Wells Feature Class..."
gp.DeleteFeatures_management(targetFc )

# Process: Make XY Event Layer...
print "Making XY Event layer..."
gp.MakeXYEventLayer_management(XYTblOrView, "longitude", "latitude", inMemXYLyr, "GEOGCS['GCS_WGS_1984',DATUM['D_WGS_1984',SPHEROID['WGS_1984',6378137.0,298.257223563]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]];IsHighPrecision")

# Process: Feature Class to Feature Class...
print "Generating temp feature class from in-memory XY table..."
gp.FeatureClassToFeatureClass_conversion(inMemXYLyr, sdeConn, tmpFc, "", ..., "")

# Process: Project...
print "Projecting feature class to Web Mercator..."
gp.Project_management(tmpFcFull, targetFc, "PROJCS ... ")

Answer 2

I am not aware that what you are asking is possible. The ESRI method is to add guids (uuid) and then enable db-sync.
Still scratching my head. since the originating data is not in gdb format. You are probably going to have to utilize rdbms tools (non-ESRI) to make it work.
This link begins familiarity with gdb-replication in which several types of data update can occur (1-way, 2-way).
Working with geodatabase replication
However I believe both dbs would need to be registered with sde (in fact 99% certain).

You might have to simply use plsql or other tool to write an update query and then run the proper tools in sde to update the sde metadata.
update db from db

It has been a while since I have messed with this but here are the sde commands for registering and indexing the sde tables ESRI link

I would suggest contacting ESRI support for better direction on the exact steps. That is unless someone gives a better answer here.