SQL Server 2005 - best way to insert new rows and update existing at same time

Hello all.  I am using 2005 and XML string to insert and update and using a If Exists, the problem is it takes a long time for the update.  Is there a faster way?  Here is what I have..
ALTER PROCEDURE [dbo].[spImportK1SummaryTotal](@xmlString xml) AS
 
declare @fundid int
declare @partnerid int
declare @partnershipid int
declare @taxperiodid int
declare @lineid int
declare @section int
declare @val1 float
     
--Update Allocation Amounts
;WITH XMLTable AS (
   SELECT D.element.value('@fundid', 'int') as fundid, 
		  D.element.value('@partnerid', 'int') as partnerid,
	      D.element.value('@partnershipid', 'int') as partnershipid, 
		  D.element.value('@taxperiodid', 'int') as taxperiodid,
		  D.element.value('@lineid', 'int') as lineid,
	      D.element.value('@section', 'int') as section,
		  D.element.value('@val1', 'float') as val1
 FROM @xmlString.nodes('/Root/Alloc') AS D(element)
)
select @fundid=XMLTable.fundid, @partnerid=XMLTable.partnerid , 
	   @partnershipid=XMLTable.partnershipid, @taxperiodid=XMLTable.taxperiodid ,
	   @lineid = XMLTable.lineid, @section=XMLTable.section, 
	   @val1=XMLTable.val1 from XMLTable
;WITH XMLTable AS (
      SELECT      D.element.value('@fundid', 'int') as fundid,
				  D.element.value('@partnerid', 'int') as partnerid,
			      D.element.value('@partnershipid', 'int') as partnershipid,
			      D.element.value('@taxperiodid', 'int') as taxperiodid,
                  D.element.value('@lineid', 'int') as lineid,
                  D.element.value('@section', 'int') as section,
                  D.element.value('@val1', 'float') as val1
      FROM      @xmlString.nodes('/Root/Alloc') AS D(element)
      Where      EXISTS(
                  select      1
                  from      K1SummaryTotal
                  where      FundID = D.element.value('@fundid', 'int')
							  and PartnerID = D.element.value('@partnerid', 'int')
							  and PartnershipID = D.element.value('@partnershipid', 'int')
						      and TaxPeriodID = D.element.value('@taxperiodid', 'int')
                              and LineID = D.element.value('@lineid', 'int')
                              and Section = D.element.value('@section', 'int'))
      )
      UPDATE      K1SummaryTotal
      SET            Amount = XMLTable.val1
      FROM      K1SummaryTotal
                  INNER JOIN XMLTable on XMLTable.fundid=K1SummaryTotal.FundID
												AND XMLTable.partnerid=K1SummaryTotal.PartnerID
												AND XMLTable.partnershipID=K1SummaryTotal.PartnershipID
												AND XMLTable.taxperiodID=K1SummaryTotal.TaxPeriodID
                                                AND XMLTable.lineid=K1SummaryTotal.LineID
												AND XMLTable.section=K1SummaryTotal.Section
 
;WITH XMLTable AS (
      SELECT      D.element.value('@fundid', 'int') as fundid,
				  D.element.value('@partnerid', 'int') as partnerid,
			      D.element.value('@partnershipid', 'int') as partnershipid,
			      D.element.value('@taxperiodid', 'int') as taxperiodid,
                  D.element.value('@lineid', 'int') as lineid,
                  D.element.value('@section', 'int') as section,
                  D.element.value('@val1', 'float') as val1
      FROM      @xmlString.nodes('/Root/Alloc') AS D(element)
      Where      NOT EXISTS(
                  select      1
                  from      K1SummaryTotal
                  where      FundID = D.element.value('@fundid', 'int')
							  and PartnerID = D.element.value('@partnerid', 'int')
							  and PartnershipID = D.element.value('@partnershipid', 'int')
						      and TaxPeriodID = D.element.value('@taxperiodid', 'int')
                              and LineID = D.element.value('@lineid', 'int')
                              and Section = D.element.value('@section', 'int'))
)
      INSERT      K1SummaryTotal (FundID, PartnerID, PartnershipID, TaxPeriodID, LineID, Section, Amount)
      SELECT      XMLTable.fundid, XMLTable.partnerid, XMLTable.partnershipid, XMLTable.taxperiodid, 
				  XMLTable.lineid, XMLTable.section, XMLTable.val1 from XMLTable

Open in new window

sbornstein2Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rpkhareCommented:
Working with XML files is slow.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SharathData EngineerCommented:
If possible change your code with MERGE statement.
0
sbornstein2Author Commented:
how do I use the merge?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.