April 5, 2006 at 9:57 am
I have a stored procedure that I am using to do bulk inserts and updates I would like to do bulk deletes also using a not in statement but am not sure about the syntax using openXML can someone assist me with that?
I thought the insert statement could just be reversed.
Thanks
Dan
CREATE PROC sp_ADUserUpdateXML @ADData nText
AS
DECLARE @hDoc int
exec sp_xml_preparedocument @hDoc OUTPUT,@ADData
--This code updates old data.
UPDATE AdUsers
SET
AdUsers.Logon = XMLEmployee.Logon,
AdUsers.UserName = XMLEmployee.UserName
AdUsers.Dept = XMLEmployee.Dept
AdUsers.DisplayName = XMLEmployee.DisplayName
FROM OPENXML(@hDoc, 'NewDataSet/ADUsers')
WITH (Logon char(10), UserName varchar(255), Dept varchar(255), DisplayName varchar(255), EmailAddress varchar(255)) XMLADUsers
WHERE AdUsers.Logon = XMLEmployee.Logon
--This code inserts new data.
Insert Into AdUsers
SELECT Logon, Replace(UserName,'.',' ') UserName,Dept,DisplayName,EmailAddress
FROM OPENXML (@hdoc, '/NewDataSet/ADUsers',1)
WITH (Logon char(10), UserName varchar(255), Dept varchar(255), DisplayName varchar(255), EmailAddress varchar(255)) XMLADUsers
Where XMLADUsers.Logon Not IN (Select Logon from AdUsers)
--This code will delete old data.
DELETE FROM AdUsers
SELECT Logon,UserName,Dept,DisplayName,EmailAddress
FROM AdUsers
EXEC sp_xml_removedocument @hDoc
GO
April 5, 2006 at 10:35 am
This thread has been posted in 2 places.
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=21&messageid=271326&post=true
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply