I have a SDE table in Postgres that was created from data in a Hosted feature layer and MS Access table. Since it was created new records have been added to the the MS Access table. I am looking for the best way to update the SDE table with only the new records from the MS Access table.
Solved
Update SDE Geodatabase from Access DB
+1- Participant
- 14 replies
Best answer by erik_jan
In general this is the process:
Read both the Access and SDE tables in FME.
Add a ChangeDetector transformer and connect SDE to the Original port and the Access table to the revised port.
Check only the key attributes that are available in both tables (see settings and "Check Attributes").
The Inserted output will give you the Access records that are not yet in the SDE table.
These can be written to the SDE table in Insert mode.
Hope this helps.
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+22- Contributor
- 2179 replies
- Best Answer
In general this is the process:
Read both the Access and SDE tables in FME.
Add a ChangeDetector transformer and connect SDE to the Original port and the Access table to the revised port.
Check only the key attributes that are available in both tables (see settings and "Check Attributes").
The Inserted output will give you the Access records that are not yet in the SDE table.
These can be written to the SDE table in Insert mode.
Hope this helps.
+1- Participant
- 14 replies
In general this is the process:
Read both the Access and SDE tables in FME.
Add a ChangeDetector transformer and connect SDE to the Original port and the Access table to the revised port.
Check only the key attributes that are available in both tables (see settings and "Check Attributes").
The Inserted output will give you the Access records that are not yet in the SDE table.
These can be written to the SDE table in Insert mode.
Hope this helps.
Thank you I will give this a try.
+1- Participant
- 14 replies
@erik_jan the concept is working great. I am running into what UI believe is a date formatting issue and I'm not sure how to deal with it. If I run the workspace based soley on Feature ID it returns 31 updates (31 new records) however when I compare Feature ID and Date it returns 300 new records ( the enitire Access table). I can only assume it is because the dates are matching making the workbench not match any of rhe records. Am I on the right path and do you (or anyone) have any ideas how to deal with this.
Thanks,
Chris M
+22- Contributor
- 2179 replies
@erik_jan the concept is working great. I am running into what UI believe is a date formatting issue and I'm not sure how to deal with it. If I run the workspace based soley on Feature ID it returns 31 updates (31 new records) however when I compare Feature ID and Date it returns 300 new records ( the enitire Access table). I can only assume it is because the dates are matching making the workbench not match any of rhe records. Am I on the right path and do you (or anyone) have any ideas how to deal with this.
Thanks,
Chris M
Hi Chris,
If you run into date formatting issues, I would add a DateFormatter transformer to both input streams, before the ChangeDetector and make sure both sets of data have the same date formatting.
Hope this helps.
+1- Participant
- 14 replies
@erik_jan the concept is working great. I am running into what UI believe is a date formatting issue and I'm not sure how to deal with it. If I run the workspace based soley on Feature ID it returns 31 updates (31 new records) however when I compare Feature ID and Date it returns 300 new records ( the enitire Access table). I can only assume it is because the dates are matching making the workbench not match any of rhe records. Am I on the right path and do you (or anyone) have any ideas how to deal with this.
Thanks,
Chris M
Okay I must be doing something wrong. When I specify just the FacilityID I get a subset of my Access table. However, the feature maybe revisited and I still want to capture these records that have the same FacilityID but different dates. So I figure no problem select both the FacilityID and Survey_Date field figuring this would meet my needs. I run the workbench and now it passes all the records from the Access DB to the SDE table not just the new ones. Any suggestions where I am going wrong?
+22- Contributor
- 2179 replies
Okay I must be doing something wrong. When I specify just the FacilityID I get a subset of my Access table. However, the feature maybe revisited and I still want to capture these records that have the same FacilityID but different dates. So I figure no problem select both the FacilityID and Survey_Date field figuring this would meet my needs. I run the workbench and now it passes all the records from the Access DB to the SDE table not just the new ones. Any suggestions where I am going wrong?
Did you try formatting both dates (from both sources) to have the same format? Not sure that the format in SDE and Access are the same.