topic Re: Using SQL update to update featureclass in Data Management Questions

Using SQL update to update featureclass

QuinnMcCarthy — Fri, 23 Mar 2012 14:43:38 GMT

I have 400 polygons that need a attribute update and not looking forward to doing it 1 by 1. OUr data is based in SQL Server 2008 and I am wondering if anybody uses SQL update command to update FC? I need to update the GIS fc from data in another table. So I am looking at something like this.

update VectorReplica.dbo.PARCELS set PARCEL_ID = new_pid from Ranieranneaxparcels
where VectorReplica.dbo.PARCELS.PARCEL_ID = Ranieranneaxparcels.NEW_PID

I have done some updates on small basis but nothing like this.

I am working on the child of the replica with a file GDB backup too for safety reasons.

Thanks

Quinn

Re: Using SQL update to update featureclass

JonDeRose — Fri, 23 Mar 2012 16:33:23 GMT

Quinn,

I would be weary of editing the backend SQL base tables for the sake of convenience. While there are editing scenarios that would accommodate this, a replica is not one of them. The replica synchronization depends on edits being made to the delta tables which are then flagged and sent over the the relative replica upon the sync operation. Editing the base table directly would not update these deltas...as a result the edits would never be sent over to the parent replica.

- Jon

Re: Using SQL update to update featureclass

QuinnMcCarthy — Fri, 23 Mar 2012 17:38:42 GMT

So the next time I take the replica down for work can I do it to the parent then and have it work?

Or should I just buck up and get to work?

Thanks

Quinn

Re: Using SQL update to update featureclass

JonDeRose — Fri, 23 Mar 2012 18:49:57 GMT

A full compress *can* pull data from the business tables into your default version yet there are many areas where error can be introduced if your workflow is not completed precisely. In theory you could edit the business tables if the data was not registered as versioned, when using move-to-base editing, or through a careful workflow to pull these base edits into the sde.DEFAULT.

If you are sold on using SQL to edit the attributes the best move would be to use a mutliversioned view to edit these attributes within SQL Server. This would enable you to edit using the sql comands as you desire but do it within a version.

Multiversioned views incorporate database views, stored procedures, triggers, and functions to access a specified version of data in a geodatabase table using Structured Query Language (SQL).

What are multiversioned views?

Creating Multiversioned Views

Editing versioned data in SQL Server using multiversioned views and SQL

Re: Using SQL update to update featureclass

QuinnMcCarthy — Fri, 23 Mar 2012 18:58:59 GMT

Thanks

I will look into that.

Quinn

Re: Using SQL update to update featureclass

QuinnMcCarthy — Mon, 26 Mar 2012 15:01:24 GMT

jnderose,

Can MV Views be used in a 2 way replica?

Thanks

Quinn

Re: Using SQL update to update featureclass

JonDeRose — Mon, 26 Mar 2012 15:27:12 GMT

Quinn,

Edits made within a Multiversioned view behave similarly as if you were to make edits within a version in ArcMap. They create states within the delta tables and can be synchronized with a relative replica elsewhere in the same manner. There are some limitations in the type of data you may edit however:

What type of data can be edited using SQL?

- Jon

Re: Using SQL update to update featureclass

QuinnMcCarthy — Mon, 02 Apr 2012 13:50:57 GMT

jnderose,

I was thinking about my problem and using multiversioned views.

Can I get by with not using a MV view if I add all of the records from that layer and did a join field in arctoolbox? I have fields that I need

to delete first and then do a join field to get the data field I need filled in? Then syncronize and to update the parent. Would you need to do the schema changes first?

Thanks

Quinn

Re: Using SQL update to update featureclass

KenCarrier — Fri, 06 Apr 2012 16:02:00 GMT

jnderose,

I was thinking about my problem and using multiversioned views.

Can I get by with not using a MV view if I add all of the records from that layer and did a join field in arctoolbox? I have fields that I need

to delete first and then do a join field to get the data field I need filled in? Then syncronize and to update the parent. Would you need to do the schema changes first?

Thanks

Quinn

I would recommend doing the following

1. Rec and Post
2. Full compress
3. Sync replicas
4. Compress production db
5. Unregister versioned objects you need to make schema changes to
6. Perform schema and attribute updates
7. Delete the versioned data from the other replica
8. replicate only the data you made changes to as a new replica
9. Unregister both replicas
10. Create a new replica using existing data method