How to overwrite source table azure Data factory

Question

I am new to ADF. I have a pipeline which deletes all rows of any of the attributes are null. Schema : { Name, Value, Key}

I tried using a data flow with Alter Table and set both source and sink to be the same table but it always appends to the table instead of overwriting it which creates duplicate rows and the rows I want to delete still remain. Is there a way to overwrite the table.

Answer 1

Assuming that your table is SQL table, I have tried to overwrite the source table after deleting the specific null values. It successfully deleted the records but got the duplicate records even after exploring various methods.

So, as an alternate you can try the below methods to achieve your requirement:

By Creating new table and deleting old table:

• This is my sample source table names mytable.
  
  
  

• Alter transformation
  

  
  

• Give new table in the sink and in settings->post SQL scripts. give the drop command to delete the source dataset. Now your sink table is your required table. drop table [dbo].[mytable]
  
  
  

• Result table(named newtable) and old table.
  

  
  
  Source table deleted.
  
  
  

Deleting null values from source table using script activity

• Use script activity to delete the null values from source table.

  

• Source table after execution.