Best way to truncate and update non-spatial sql table

3445
6
02-15-2016 01:09 PM
ChrisPedrezuela
Occasional Contributor III

Hi guys,

Need so inputs or advice on this, I have a sql server table that I need to sometimes truncate (completely wipe out data) or delete several rows from periodically. Then I also need to insert new rows based on the rows in a featureclass. I don't have a problem updating it,  I just use arcpy.da.InsertCursor but maybe this is not the best? For deleting a row or totally wiping out the table row, should I be using ArcSDESQLEXECUTE? I tried using data management>table>delete rows but it seems to be just applicable to esri tables and features.  I actually tried ArcSDESQLEXECUTE, passed a sql statement like "DELETE * FROM TABLE", but I keep hitting this "Sream" type error.

I need to setup my python code to run this kind of admin task on the sql table.

Hope you guys can give some tips. Thanks

0 Kudos
6 Replies
VinceAngelo
Esri Esteemed Contributor

"DELETE * FROM table" is a SQL syntax error.  You should not use DELETE when TRUNCATE is appropriate, though the difference only really manifests in large tables (over 100k rows)

There are two geoprocessing tools available for delete and truncate, though they may require that the table be registered with ArcSDE before they will function.

- V

0 Kudos
ChrisPedrezuela
Occasional Contributor III

Hi Vince,

If its a sql server table, how do you register it with arcsde since they are non-spatial?

Thanks

0 Kudos
VinceAngelo
Esri Esteemed Contributor

Any table can be registered with the geodatabase via right-click "Register with geodatabase", provided you're willing to have an SDE-set registered rowid column.

Any table with a unique not-NULL integer column can be registered with a USER-set rowid column via sdetable -o register- (actually, any table can be registered without a rowid, but ArcGIS might be expecting a rowid column)

0 Kudos
ChrisPedrezuela
Occasional Contributor III

Tried to right-click on the table but when I go to manage, all is greyed out. This sql database is actually managed by someone else and have tables that are being used in another business process. The table im trying to update will be an input for a reporting process using reporting tool. probably I should be asking our dba for some inputs as well. But since I can use insertcursor, should it be possible for me to use updatecursor to delete rows? or this is very inefficient?

0 Kudos
VinceAngelo
Esri Esteemed Contributor

You must be the table owner to register a table.

Have you tried using the delete or truncate tools?  You might need to be owner to truncate, or it might just be a required GRANT.

DELETE is always inefficient, because it's a logged transaction.

- V

0 Kudos