topic Re: Update an registered table with SQL? in Data Management Questions

Update an registered table with SQL?

gismoe — Wed, 29 Dec 2010 08:38:44 GMT

I have to alter (truncate and refill) a table on my SQL Server 2008 every night. The table is used as event layer; so an objectid is needed.

But trying to "select...... into.....from" doesn't work because the objectid is missing.

I found this page Editing nonversioned geodatabase data in SQL Server using SQL but this seems to work only for one dataset. I have to alter 30.000 datasets with an unique ID and i don't want to write an SQL-Statment with 30.000 lines.

So my simple question: How to alter an registered table with "select...... into.....from"?

Because of the lost of permissions i don't want to delete and create the table.

Any suggestions?

Re: Update an registered table with SQL?

VinceAngelo — Wed, 29 Dec 2010 10:52:48 GMT

Generally "update" means something different when referring to SQL.

Do you mean 30k *rows* or 30k *tables*?

If you replace the table contents daily you should consider registering the table with
a USER-set rowid.

Why don't you just take the extra step to make a point layer from the table? Event
layers don't have a spatial index, which means they are generally slower. Loading
30k points into ArcSDE takes just seconds, and the API takes care of the registered
column.

- V

Re: Update an registered table with SQL?

gismoe — Wed, 29 Dec 2010 17:48:46 GMT

Oh yes, you're right. My SQL-terms are unintelligible.

I have to replace the table contents of 30.000 rows daily with BIDS. The table has to be used by different software applications. ArcGIS is the only GIS. Other applications wouldn't like a feature class. I think the speed is ok.

Maybe i want to create a FC later... Is there a command to make a point feature class, so it can run in batch?

Otherwise i was able to register/unregister my table with the sdetable-command. Thanks a lot!
BTW: Why can I do not set the USER-set rowid with ArcCatalog?

I'am wondering that some commands are only possible with ArcGIS/ArcCatalog and others only with this sde-tools.

Re: Update an registered table with SQL?

VinceAngelo — Thu, 30 Dec 2010 10:21:05 GMT

ArcObjects can specify a USER-set objectid, it's just not in the GUI.

- V

Re: Update an registered table with SQL?

VinceAngelo — Thu, 30 Dec 2010 10:23:09 GMT

If you use SQL-Server 2008, you can use the native GEOMETRY type.

- V

Re: Update an registered table with SQL?

gismoe — Thu, 30 Dec 2010 11:13:44 GMT

Can you elaborate on this?

I found this example:

CREATE TABLE SpatialTable
    ( id int IDENTITY (1,1),
    GeomCol1 geometry,
    GeomCol2 AS GeomCol1.STAsText() );
GO

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('point ((100 100))', 0));
GO

But how can ArcGIS display that?

Re: Update an registered table with SQL?

VinceAngelo — Thu, 30 Dec 2010 12:18:30 GMT

Look at the documentation for registering a layer with ArcSDE, or if you're
using ArcGIS 10, for query layers.

- V

Re: Update an registered table with SQL?

gismoe — Thu, 30 Dec 2010 12:24:47 GMT

Registering a layer works pretty good but how can i display a table with a GEOMETRY type?

Re: Update an registered table with SQL?

VinceAngelo — Thu, 30 Dec 2010 12:32:36 GMT

When you register a *layer* (not table) with ArcSDE, an entry is made in the
LAYERS table for that GEOMETRY column, making it availbale to ArcGIS for
geodatabase participation. Query layers view GEOMETRY columns without
registration.

- V

Re: Update an registered table with SQL?

gismoe — Thu, 30 Dec 2010 13:06:22 GMT

Thank you very much. It works!
Sorry but i'am a rookie with this tools and SQL Server an it took a while from "register a *layer*" to sdelayer -register .....