Skip to main content
Solved

Combine multiple sql tables into 1 table with different schema

  • November 15, 2021
  • 2 replies
  • 129 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.

View original
Did this help you find an answer to your question?

2 replies

ctredinnick
Supporter
Forum|alt.badge.img+18
  • ctredinnickSupporter
  • Supporter
  • 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
  • December 1, 2021

Thanks! AttributeManager ended up being the better option.

Reply


Helpful Members This Week

Community Stats

31,007
Posts
117,520
Replies
38,787
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
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.