Removing cursor

• legeboka

  Mr or Mrs. 500

  Points: 588

  More actions

  May 22, 2017 at 12:03 pm

  #342938

  Hi,

  I have a store procedure that uses cursor to update tablewith 5 million records. It’s extremely slow and expensive. Is there way toremove the cursor and speed up the query. The goal is set parent record tochild. Row to row linked by ParentID value

  Here’s conditions: If DocType=’A’ than update ParentFlagwith value from parentID column; If doctype<>A then move to the nextrecord and check the DocType. As soon as DocType=A update all previous recordsin this chain with parentID value, If Doctype ‘A’ not found leave it NULL.

  Below is example:

  
  

  IF (SELECT OBJECT_ID('tempdb..#Test'))is not null

      DROP TABLE #Test

  Create Table #Test (

  PK int IDENTITY(1,1) NOT NULL,

  DocID Varchar(10),

  ParentID Varchar(10),

  DocType Varchar(10),

  ParentFlag Varchar(10)

  )

   

  Insert into #Test(DocID, ParentID, DocType)

  SELECT '146F','147F','S'

  UNION ALL

  SELECT '147S', '161S', 'S'

  UNION ALL

  SELECT '161S', 'R-12', 'S'

  UNION ALL

  SELECT 'R-12', 'AST', 'A'

  UNION ALL

  SELECT '3','4','W'

  UNION ALL

  SELECT '4','5', 'W'

  UNION ALL

  SELECT '9A', '55W','A'

   
  Need help with update statment

  UPDATE #Test SET ParentFlag='R-12' WHERE PK=1

  UPDATE #Test SET ParentFlag='R-12' WHERE PK=2

  UPDATE #Test SET ParentFlag='R-12' WHERE PK=3

  UPDATE #Test SET ParentFlag='R-12' where PK=4

  UPDATE #Test SET ParentFlag='55W' where PK=7

   
  Expected outcome outcome

  SELECT * from #Test

  Thank you for your help

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  May 22, 2017 at 12:32 pm

  #1943627

  Duplicate post.
  Further replies in here: https://www.sqlservercentral.com/Forums/1877500/Cursor-removal

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2