Forum Discussion
"Upsert" write behaviour applied to a dataverse table
Hello Community,
I'm actually trying to implement a copy from my lakehouse to a dataverse table using a copy activity in a pipeline, i already have data in my dataverse table to update it every month, but i need to understand two points about the upsert behavior :
- Upsert Logic: An upsert updates a row if it exists or inserts a new one if it doesn't. but which column is this matching based on?
- Alternate Key: Is the "Alternate key name" field in the copy activity the one used to perform this upsert matching? I'd like to use it, but the dropdown only shows "None", do i need to configure something on my dataverse table level ?
Thank you guys for your help !
Hello ABSOmar
Upsert matches on Alternate Key (if specified), otherwise on the primary key (GUID)In the Destination → Advanced settings for the Dataverse connector, Write behavior must be Upsert. For matching:- If you provide an “Alternate key name”: The copy activity will match existing rows using that alternate key (a unique combination of one or more columns you’ve defined on the target table). If a match is found, it updates; otherwise it inserts
- If you leave “Alternate key name” empty: The upsert falls back to the table’s primary key (GUID)—i.e., the logical <entity>id column—based on the value you supply in the incoming data for that column. If the GUID matches an existing row, it updates; if the GUID is new, it inserts.
Hope this helps! Kindly Accept this as a Solution or a Thumbs Up!
2 Replies
Hello ABSOmar
Upsert matches on Alternate Key (if specified), otherwise on the primary key (GUID)In the Destination → Advanced settings for the Dataverse connector, Write behavior must be Upsert. For matching:- If you provide an “Alternate key name”: The copy activity will match existing rows using that alternate key (a unique combination of one or more columns you’ve defined on the target table). If a match is found, it updates; otherwise it inserts
- If you leave “Alternate key name” empty: The upsert falls back to the table’s primary key (GUID)—i.e., the logical <entity>id column—based on the value you supply in the incoming data for that column. If the GUID matches an existing row, it updates; if the GUID is new, it inserts.
Hope this helps! Kindly Accept this as a Solution or a Thumbs Up!
Very clear, Thank you deborshi_nag !
