Dynamic schema table updates with computed columns
I'm trying to move data from one database to another. I have over 180 tables that are mostly similar, however 26 of them have different columns from the rest.
I've played around with dynamic schema and have that working, however I ran into a problem with SQL computed columns. As expected, a computed column can't be directly inserted to or updated.
Is there any way to exclude these columns from the dynamic column list? Ideally I'd like to exclude them based on the computed property from SQL. Or, I can hard-code a list of the column names if necessary... but I'm not sure how to "extract" a hard-coded list of column names from the dynamic list and exclude them from the update statement.
Thanks shong for your response - that's helpful. After playing around a bit more I was able to figure out how to do what I want using tExtractDynamicFields and adding in a hard coded list of columns that I want to ignore in a tMap that follows. But the blog shows an option that would let me play with the columns based on column properties. I'll circle back around and try that out!
For anyone else that finds this thread and may be wondering what I did, here are my steps:
tDBInput to read all rows from my source table
tExtractDynamicFields lets me list out the columns I want and keep all other columns in a dynamic field
tMap let's me indicate which fields to pass through
tDBOutput to Insert or Update to my target table
1 tDBInput: - use a "Select *" to retrieve all columns
My schema looks like this:
In my example, all 140 tables have id, code & description. The tDBInput component is smart enough to load those columns into my defined columns, and put "everything else" into the "other_columns" dynamic field.
2 In the tExtractDynamicFields component I've included my common fields, all of the potential "computed" type fields that might be present in my tables, and the "other_columns at the end. Again, Talend is smart enough to extract those columns present in my source "other_columns" dynamic field, map them to the new column name that matches, and leave everything else in the "other_columns" field at the end.
3 In tMap I'm simply leaving the computed columns behind and only passing through the ones that I want. "other_columns" has everything else for that particular table.
4 Finally tDBOutput does an Insert of Update with known columns, and the unknown columns ("other" -> dynamic type)
I hope this helps someone! It took me a bit of research to put it together.
