Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I have a table in a MSSQL Server Enterprise SDE database. I need to join a table from a MS SQLExpress Workgroup SDE database and then calculate 11 fields in the Enterprise table based on values in the Workgroup Table then remove the join.

I have tried to do this in model builder add it takes forever to run. I thought of selecting only those records that don't match before performing the calculate, but then thought maybe there were other ways to tackle this problems.

So now I am thinking that there are three alternatives that will do what I need in a quicker manor. My question is can anyone give insight on which would be faster or easier to do.

Option 1 Create a python code in ArcGIS 10.1 to perform the join select the mismatched records and perform the calculation then move on to the next field, when all done remove the join.

Option 2 Use FME Desktop to perform the same steps outlined above.

Option 3 Use SQL and perform the same steps above in Database

An input or suggestions would be appreciated.

share|improve this question
    
What version of ArcGIS are you using 10.0 or 10.1? If you go with Option 1 then perhaps see if this Answer to another Question helps. –  PolyGeo Feb 4 '13 at 22:02
    
I have edited the question to indicate I am working in ArcGIS 10.1 –  Chris M Feb 5 '13 at 14:33
add comment

2 Answers 2

up vote 3 down vote accepted

While FME would likely work very effectively for this, I'd lean towards using native MSSQL tools.

This answer on Stack Overflow talks about using Linked Servers to communicate between SQL and SQL Express instances:

Linked server setup between SQL Server Express and SQL Server

I haven't tested this myself, but it should work on current versions of SQL Server. You will need admin rights on one of the servers to set up the Link. Once established, you can either use four-part notation to access the linked server ( [server].[database].[schema].[table]) or look into the OpenQuery commands.

OpenQuery (Transact-SQL) Documentation

From there, you should be able to build a traditional SQL UPDATE query to do what you need in a single step.

In either case, test on a copy of the data first... easy for things to go horribly bad if you get a step wrong.

share|improve this answer
add comment

There are some benefits to using FME for non-spatial data transformations like this. For one, it can be easier for non SQL wizards to understand and maintain the process.

In FME you would use a FeatureMerger to join the tables, then a Tester to identify the records to update, an AttributeCreator to update them, and then finally a Writer back to the database with the mode set to "Update" and the update key specified (the table needs to have primary key).

I also recommend the new webinar on non-spatial http://www.safe.com/videos/?video=http://cdn.safe.com/videos/Webinar-Transform-Non-Spatial-Data-Using-FME.mp4&category=webinar

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.