ADF copy data table and update data source table

Question

I'm trying in ADF to copy data from table_a to table_b and when successfully copying data to table_b, data in table_a is updated for field is_imported value set to 1.
how to update table_a when successfully copy data from table_a to table_b?

structure :

table_a
--------
table_a_id
unique_id
name
is_imported

table_b
--------
table_b_id
unique_id
name

query source :

select temp.* from table_a as temp where temp.is_imported = 0

Answer 1

Hey,
Assuming the tables A and B are in different databases, you can use the below flow

copy activity>> on success>> Script activity

The copy activity would copy the data from table A to table B :
Reference: https://marlonribunal.com/copy-data-from-on-premise-sql-server-to-azure-database-using-azure-data-factory/

On successful execution of copy activity, use Lookup/SCript activity to update Table A
with below query:

update table A
Set temp.is_imported = 1

In case if the tables are in same database, you can create a stored procedure to hanlde everything

Answer 2

Hey Since both the tables are in same database, create a Stored procedure somewhat like below

Begin transaction

Insert into table B
Select columns from table A where colu=0 ;

Update Table A
Set Colum=1

End transaction.

You can use the stored procedure activity within ADF to trigger the stored procedure

Answer 3

In my case, the tables are in the same database.

So for Write behavior on sink dataset select stored procedure?