Skip to main content
Solved

Combine multiple sql tables into 1 table with different schema

  • November 15, 2021
  • 2 replies
  • 172 views
rtreecare
Contributor
Forum|alt.badge.img

I have to combine 30 sql tables into 1 while preserving globalID unique identifiers. These tables have slight differences in column names (i.e. GlobalID vs globalid or JobsiteGlobalID vs JobGUID). These need to be mapped during the insert.

I want to be able to run this multiple times so I'm looking for an "if not exists" append transformer. Any recommendations? I've looked as FeatureMerger, but it doesn't seem to work with 30 readers. I've tried a simple append, but re-running causes issues.

Thanks.

Best answer by ctredinnick

If you need to combine multiple schemas into one and it's just attribute name differences, you can use an AttributeRenamer or AttributeManager. Basically, if you were to choose GlobalID to be the one attribute to hold all the GUIDs, use that to rename globalid to GlobalID, rename JobsiteGlobalID to GlobalID, and rename JobGUID to GlobalID. All GUIDs will end up named GlobalID. Note there was a small bug with this approach in 2020, so if you have that version you'll need to set the Default Value on the rename to be GlobalID.

Then, for the "if not exists", you can use a DatabaseJoiner to try join to the output table, joining on the GlobalID. If the feature comes out the Unjoined port, that means it has a new GlobalID and you can write out that data as normal.

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.

2 replies

ctredinnick
Supporter
Forum|alt.badge.img+19
  • ctredinnickSupporter
  • Supporter
  • 225 replies
  • Best Answer
  • November 30, 2021

If you need to combine multiple schemas into one and it's just attribute name differences, you can use an AttributeRenamer or AttributeManager. Basically, if you were to choose GlobalID to be the one attribute to hold all the GUIDs, use that to rename globalid to GlobalID, rename JobsiteGlobalID to GlobalID, and rename JobGUID to GlobalID. All GUIDs will end up named GlobalID. Note there was a small bug with this approach in 2020, so if you have that version you'll need to set the Default Value on the rename to be GlobalID.

Then, for the "if not exists", you can use a DatabaseJoiner to try join to the output table, joining on the GlobalID. If the feature comes out the Unjoined port, that means it has a new GlobalID and you can write out that data as normal.

rtreecare
Contributor
Forum|alt.badge.img
  • rtreecareContributor
  • Author
  • Contributor
  • 15 replies
  • December 1, 2021

Thanks! AttributeManager ended up being the better option.

Community Stats

32,062
Posts
121,727
Replies
39,689
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.