I have a (odd?) requirement where I need to sync a table with an XML document. This is being done client side now via recrodsets and works fine but is too slow.
Given an XML document that represents a table, I need to manipulate a similar target table that matches the XML structure (but has additional fields) such that:
- Any rows in the table not in the XML are deleted
- Any rows in the XML that are not in the table are added to the table
- Any rows in the XML that match (via the PK columns in the XML) the table are updated.
The target table has additional columns that are not in the XML, so I can't delete the target table and rebuild it from the XML or data loss would occur.
I don't have an issue with the DELETE, INSERT, and UPDATE being done in three separate steps, or even moving the XML into a temporary table if that facilitates the process. With that in mind, I know how to move the XML into a table:
Given the source XML document @XML
<root> <row> <pkcol1>99</col1> <pkcol2>AAAA</vol2> <col3>Update this!</col3> <row> <row>..</row> <row>..</row> <row>..</row> and so on... </root> CREATE TABLE Source ( pkcol1 INT, --match this pkcol2 VARCHAR(30), --match this col3 VARCHAR(64), -- update this! ); INSERT INTO Source (pkcol1, pkcol2, col3) SELECT DISTINCT 'pkcol1' = x.v.value('pkcol1', 'INT'), 'pkcol2' = x.v.value('pkcol2', 'VARCHAR(30)'), 'col3' = x.v.value('col3', 'VARCHAR(64)'), FROM @XML.nodes('/root/row')x(v)
My target table has all the same named and typed columns as the XML plus additional columns that must be preserved.
An example target table:
CREATE TABLE Target ( pkcol1 INT, --match this pkcol2 VARCHAR(30), --match this col3 VARCHAR(64), -- update this! col4 VARCHAR(64) --preserve this! col5 VARCHAR(64) --preserve this! col6 VARCHAR(64) --preserve this! col_N ...and so on );
Breaking the problem into DELETE, INSERT and UPDATE seems reasonable to me, and I think moving the XML into a temporary table would simplify things.
So given a temporary source table with columns that are a subset of the target table, how would I DELETE any rows in the target that are not in the source? (I'm sure this is wrong due to the multiple PKs)
DELETE FROM Target t WHERE t.pkcol1, t.pkcol2 NOT IN (SELECT s.pkcol1, s.pkcol2 FROM Source s)
The UPDATE (again, definitely wrong):
UPDATE Target t SET t.col3 = s.col3 WHERE t.pkcol1=s.pkcol1 AND t.pkcol2 = s.pkcol2
INSERT INTO Target t (t.pkcol1, t.pkcol2, t,col3, t.col4, t.col5) VALUES (s.pkcol1, s.pkcol2, s.col3, '','')
Any insights you can provide are greatly appreciated!