[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2008-11-10
3
Medium Priority
?
307 Views
Last Modified: 2013-11-05
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
Comment
Question by:sbornstein2
3 Comments
 
LVL 8

Accepted Solution

by:
rpkhare earned 2000 total points
ID: 22928142
Working with XML files is slow.
0
 
LVL 41

Expert Comment

by:Sharath
ID: 22928443
If possible change your code with MERGE statement.
0
 

Author Comment

by:sbornstein2
ID: 22965868
how do I use the merge?
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month18 days, 19 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question