Best way to truncate and update non-spatial sql table

Question

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

VinceAngelo · Answer

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

ChrisPedrezuela · Answer

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?

VinceAngelo · Answer

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)