Select to view content in your preferred language

How to find records created, updated or deleted in a version

227
4
3 weeks ago
SanchezNuñez
Occasional Contributor

Good morning,

I am looking for samples in SQL or arcpy python to identify records added, updated and deleted in a version.

In a traditional version or a branch version.

Thanks

 

 

0 Kudos
4 Replies
jcarlson
MVP Esteemed Contributor

We don't use traditional versioning, but in branch versioning, you can get at the information with SQL.

select
    distinct on (globalid)
        globalid as,
        gdb_archive_oid,
        gdb_is_delete,
        last_edited_date
    from
        sdo.table_name
    where
        gdb_branch_id = 0 -- or put in the branch id of the version you want
    order by
        globalid,
        gdb_archive_oid desc
)

You can use the results to identify features edited or deleted in a given version. You'd have to add to the expression to get it to compare against the default version, or else use some additional filter statements to limit edits by date range, or whether it's the first entry for that globalid, etc.

We use this for a number of scripts, and we just use pandas.read_sql() to pull that information into a dataframe.

- Josh Carlson
Kendall County GIS
JoseSanchez
Regular Contributor

This post explains how to query a traditional version:

View number of edits made in SDE.Default - Esri Community

0 Kudos
SanchezNuñez
Occasional Contributor

Good morning @jcarlson 

Could you please share a sample code using  pandas.read_sql()  in python?

Thank you

0 Kudos
jcarlson
MVP Esteemed Contributor

It's really as simple as wrapping my example SQL in that function, plus defining your DB connection. (See here for how to define your connection.)

sql = """
select
    distinct on (globalid)
        globalid as,
        gdb_archive_oid,
        gdb_is_delete,
        last_edited_date
    from
        sdo.table_name
    where
        gdb_branch_id = 0 -- or put in the branch id of the version you want
    order by
        globalid,
        gdb_archive_oid desc
)
"""

# connection string or object
conn = "your db connection here"

df = pandas.read_sql(sql, conn)

 

- Josh Carlson
Kendall County GIS