How to merge data in ArcSDE using SQL

[RachelS]

Hi,

I have datasets with one to many relationships to tables.

These identical datasets and tables are in geodatabases by year.

I'd like to merge the years as the schema is the same every year.

However, I see if I merge them, the object ID changes and this is the only link I have to one of the tables REl_OBJECTID.

The table shows photos uploaded.

Is there any other way I can merge them, or will I just leave them separate for each year?

Thanks

R

[AsrujitSengupta]

Even if you try to merge them and are somehow able to maintain the same ObjectIDs, wont the features in the merged data try the get the same ObjectIDs?

In other words, I guess in your scenario, it will be difficult to merge the data and maintain the same ObjectIDs...so I'll suggest leaving them separate for each year.

[Venkata_RaoTammineni]

somehow better to go with any ETL tool such as safe FME or ArcGIS Data Interpolation tool ?

[Anonymous User · Accepted solution]

Hello all

I think you can try this approach:

- On each feature class/table create a new field. Populate this field with using "Field calculator", joining year+objectid (in the related table. ex. 20151234 ,for the 2015 dataset and the objectid 1234); in the related table you can use:  year+REl_OBJECTID .

Now you have an aditional field on each featureclass/table you can use to create the relatioship class. If you can identify anyway the year of each feature dataset, sure you can write an python script for automating this task.

Once you have created all the fields in the featureclasses/tables, merge them.

You can rebuild the relationship class using the new fields. Now you can decide If you continue with these fields or... simply, using the new relationship, calculate the "old" REI_OBJECTID field in the table to point to the new OBJECTID values in the feature class.

Hope this helps

Jesús de Diego

[AndyOmmen]

Hi Rachel,

Have you looked into creating a Query Layer within ArcMap? This option would allow you to join multiple feature classes and tables into one view which you could then access in ArcGIS Desktop. They are also dynamic so any updates you make will be visible within the view. You could also look into creating a database view within your RDBMS. I hope this helps.

Andy

[RachelS]

Thanks for the replies,

I followed Jesús Manuel de Diego Alarcón method and it's worked out fine for me.

Rachel