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

[SanchezNuñez]

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

[jcarlson]

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.

[JoseSanchez]

This post explains how to query a traditional version:

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

[SanchezNuñez]

Good morning @jcarlson 

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

Thank you

[jcarlson]

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)