+11Hello There
we are looking to sync MSSQL DB with PostGIS DB , we used the dynamic workflow in this article
we want to do is each time we run this workspace it will update the existing table and create a new table if needed, we configure the writer as shown, the problem how we should know match column parameter in order to execute the update,
Best answer by boubcher
@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
Hi @boubcher, In the fixed "Update" mode as shown in your screenshot, no records can be inserted into the destination table if the table doesn't exist before run, since no records to be updated don't exist in that case.
If you need to perform these two operations with a single writer feature type,
you want to control DB opration through "fme_db_operation" attribute and set the update condition (WHERE Clause) through an attribute e.g. "fme_where".
The "fme_db_operation" attribute should store "UPDATE" if the table exists, "INSERT" otherwise. So, you have to determine if the destination table exists or not at some point in the workspace at run-time and set one of "UPDATE" or "INSERT" to the attribute.
The "fme_where" attribute should store an expression that specifies a record to be updated (e.g. id = '@Value(id)'). It's easy if the expression is identical through all the tables, but you have to make efforts to define expressions dynamically if different expressions are required for each table. The solution depends on actual conditions and requirements.
See here to learn more about Feature Operations: Feature Operations
+11@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
select tablename
from pg_tables where schemaname = 'Tansiq'
@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
+11@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
do we need to follow the same process as the previous operation.
@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
Tutorial: Updating Databases
+11@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
I did creat the WS , but I am not sure about the update operation, for the existing record we need to out a where close there , but it should be like dynamic
any Help
Thanks
sync-2-database.fmw
@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
+11@takashi
if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.
is this correct
thanks for the reference
Sorry I get your message after my last comment
Thanks again