ADF copy data table and update data source table

Galih 111 Reputation points
2022-11-01T09:22:49.46+00:00

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

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,914 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nandan Hegde 30,081 Reputation points MVP
    2022-11-01T09:43:04.427+00:00

    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

    2 people found this answer helpful.
    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. Nandan Hegde 30,081 Reputation points MVP
    2022-11-01T09:57:06.917+00:00

    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

    1 person found this answer helpful.

  2. Galih 111 Reputation points
    2022-11-01T09:54:28.637+00:00

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

    So for Write behavior on sink dataset select stored procedure?

    255980-image.png

    0 comments No comments