• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 310
  • Last Modified:

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

0
sbornstein2
Asked:
sbornstein2
1 Solution
 
rpkhareCommented:
Working with XML files is slow.
0
 
SharathData EngineerCommented:
If possible change your code with MERGE statement.
0
 
sbornstein2Author Commented:
how do I use the merge?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now