question

sujitha.kalakota avatar image
sujitha.kalakota asked

XML with Merge statement - Problem with deleting dependent tables data deletion

Hi, I'm working with XML formatted input data and using Merge statement. My intention is to completely make use of XML & Merge only. I need to insert/update/delete parent & dependent child tables data. I'm able to handle Insert & Update of child tables data using OUTPUT clause with reference to parent but when i try to delete parent table data, its throwing foreign key constraint violation which is as expected. Can some body help me with any work around to resolve this constraint violation issue and data should be deleted in all dependent tables as well.
xmldeletemergeissue
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Grant Fritchey avatar image
Grant Fritchey answered
To delete data in the dependent tables, regardless of XML or not, you have two choices. You remove the data from those dependent tables first, then delete the data from the parent table, or you enable [cascade delete][1]. This will automatically remove the child data. Personally, I would never use this because it's a very imprecise mechanism for controlling the data in your system. Plus, depending on the amount of dependent data, this can lead to very large transactions which hurt performance and could lead to severe blocking. You may also see more deadlocks because of the large transactions and because you can't control the order in which data is removed. [1]: http://msdn.microsoft.com/en-us/library/ms186973(v=SQL.105).aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the reply Grant Fritchey.. As u mentioned, cascade delete is not the right option. Using XML with Merge, how to handle this scenario is the problem here. Input XML holds, parent & its dependent child info in a single XML variable. With only two Merge statements(one for parent data and the other for child table data),i'm unable to handle this constraint issue without using cascade option.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.