Solved

Combine multiple sql tables into 1 table with different schema

  • 15 November 2021
  • 2 replies
  • 27 views
R

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.

icon

Best answer by ctredinnick 30 November 2021, 10:45

View original

2 replies

ctredinnick
Rank
Userlevel 3
Badge +16

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.

R

Thanks! AttributeManager ended up being the better option.

Reply


Community Stats

28,724
Posts
109,518
Replies
36,563
Members
Terms & ConditionsCookie 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.