December 7, 2004 at 12:34 pm
I'm fairly new to stored procs and to XML and I need some quick help on the combination of the two. I already have a PROC that accepts an XML document and does an INSERT on all of the records. But now I need to alter it such that it attempts to UPDATE the record if it exists, ELSE INSERT a new record. Below is what my procedure looks like at this point (I've cut out some fields for brevity).
Thanks in advance!
Anthony
-------------------------------------------------------------------------
CREATE PROC sp_insertRecords @PromoData ntext
AS
DECLARE @hDoc int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @PromoData
INSERT INTO promo_data
SELECT *
FROM OPENXML(@hDoc, '/PROMO_DATA/DATA')
WITH (
custom_id int './CUSTOM_ID',
first_name varchar(255) './FIRST_NAME',
last_name varchar(255) './LAST_NAME',
)
EXEC sp_xml_removedocument @hDoc
December 8, 2004 at 1:29 pm
Ok, I'll answer my own friggin' question! 🙂
I ended up abandoning OpenXML in favor of SQLXMLBulkLoader (called from a VBScript file) to suck all records into a temp table, then I called a stored procedure that syncs the two tables like this:
UPDATE promo_data SET first_name = ptemp.first_name, last_name = ptemp.last_name FROM ptemp WHERE promo_data.custom_id = ptemp.custom_id
Then similarly I used an INSERT... WHERE custom_id NOT IN...
Works great! I do have to give props to my handy Wrox book for helping me out. I was just coming on here to look for a little shortcut. 🙂
Hope this helps out someone else.
Anthony
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply