on 04-11-2022 3:50 PM
I need to update some rows in a table in Datastore_1 based on values in a table in Datastore_2.
In MSSQL I would just specify the different databases, or even linked server names in the SQL code.
Is there a way to code an update (or Join) statement that spans different DataStores ?
One datastore connects to MSSQL. The other connects to IBMi.
Basically, something like
UPDATE Datastore_1.Table1
SET Status = 'E'
WHERE Rec#1 in (Select Rec#2 from Datastore_2.Table_2)
you can read both sources and use the query transform to perform this. Or you can use lookup function in a query transform as well to lookup in the 2nd table.
Thanks,
Denise
SAP Support
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
to expand...once a query transform is developed with the data in the desired form for the target table, you can try applying the updates in one of two ways (that I know of): 1) send the output to a Map_Operation transform, map "normal" Input rows to "update" output and all other inputs to "discard", send results of the Map_Operation to the target table; or 2) send the query output directly to the target table and adjust the values on the "Options" tab values on the target table to allow a merge - Auto correct load would be Yes and Allow merge or upsert would be Yes.
User | Count |
---|---|
72 | |
9 | |
8 | |
7 | |
6 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.