Forum Discussion
updating records using copy job?
Hi,
when Data copy will support updates and not just adding rows?
I have a lot of tables which can have updated records, so I want to update my destination not just adding new rows.
thanks.
ok... unfortunately it's not supporting deletes.
and the update appear to update all the rows matching the ID without option to select a hash or timestamp column.
well... I'll still use SQL statements for now. still faster with more control than relying on external tools like the copy job.
11 Replies
There is no option for warehouse for this yet, I did see an upsert (preview) button appear on a lakehouse though. The best way for now is to do it using SQL code, whwere you check for an existing record, which you than update (UPDATE TABLE, set column = etc etc ) and when a record does not exist you will do an insert
Hi Jerome22 ,
Unfortunately, in Microsoft Fabric, the Copy Data activity does support an "Upsert" behavior. You can however use workarounds like Dataflows or SQL logic.
You can refer-
upsert option missing in copy activity of microsoft fabric data pipeline
Hope this helps!
If the response has addressed your query, please accept it as a solution so other members can easily find it.
Thank Youok... unfortunately it's not supporting deletes.
and the update appear to update all the rows matching the ID without option to select a hash or timestamp column.
well... I'll still use SQL statements for now. still faster with more control than relying on external tools like the copy job.
What you could do is incorporate a where statement in the update statement, where you do something like
"WHERE hash in (select hash from TABLE) AND time_stamp_column > [your timestamp]
The above code is rough and probaly has some syntax errors, but I hope you understand the geste of the logic behind it 🙂
CheersHans
Copy jobs in Data Factory means there is no need for updates. Overwrite where ever possible ( Data Lake and Data Lakehouse ) ( Apache Spark and Delta Lake ) or ( Merge in Microsoft Fabric or
Incremental Refresh with below code ( Append in Microsoft Fabric )
### VariablesLakehouseName = "Demo"TableName = "DimTables"ColName = "Surrogate_Key_Demo"NumberOfRowsToRemove = "10"### Remove Old rowsReload = spark.sql("SELECT Max({0})-{1} as ReLoadValue FROM {2}.{3}".format(ColName,NumberOfRowsToRemove,LakehouseName,TableName)).collect()Reload = Reload[0].ReLoadValuedisplay(Reload)## ColName should be integer at all times to work (Incremental Refresh)spark.sql("Delete from {0}.{1} where {2} > {3}".format(LakehouseName, TableName, ColName, Reload))so it means you should always use Notebooks. ( Concept of Data Lake and Data Lakehouse)We often do a clear out of tables or certain rows in tables, but we have a few datasets that are so large that is slows things down.
This can de done with just pipeline activities (copy and script activity), notebooks are not always needed. I would only use a notebook when i need to do something with a lakehouse.smeetsh We always have Notebooks. No issues. Instead of Data Factory, Use Notebooks wherever we can. Databricks has implemented Delta Lake and then Microsoft follows Databricks. ( Delta Lake is based on Python and Scala ).
I could be wrong here, but I don't think a notebook can write to a warehouse, which for us at least would be the issue and it may be as well for the topic starter
