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.
Best Answer
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.