I have an ArcSDE feature class that contains project points based on XY data from an external SQL database. (Not a geodb). I am looking for the best way to "recreate" the ArcSDE feature class from the external data on a daily basis. The external database is not not connected to ArcSDE and projects may be added or removed from the external database, which needs to be reflected in the ArcSDE feature class.
If you are using ArcGIS 10, you don't have to copy the data. You can use a Query Layer to access the non-SDE table. If your database has a supported native spatial type (such as Oracle Spatial or PostGIS), you can use the native spatial type as an ArcGIS compatible geometry. You can also use the Create XY Event Layer to convert non-spatial tables to a feature class.
If you need to do all of this programmatically, you can save the Query Layer as a .lyr file. Then you should be able to load into python, create an XY Event Layer, and then export the resulting layer to a geodatabase.
Why recreate featureclass?you can read records from external DB and update your featureclass in ArcSDE without having to drop and recreate it.Even you can create a shape file from all records in external DB,load it into your GIS application(for example ArcMap) and then intersect (or use others spatial relations like "idntical") with your feature class,then invert selection and use new selected feaures from shape file(those which are not intersect or identical to featureclass) and insert them into feature class to update it.