sql server - How to avoid cursor use in dynamic sql update? -


Therefore, I am trying to rewrite some stored processes which we use to better set-based arguments and less However, I can not come in a more effective way to the bottom without using the cursor, or to eliminate the use of the cursor due to performance problems.

Currently, what I am doing chooses to set the initial result in a temporary table which looks like

some 
  #tctable selection stuff .id, stuff.datapoint, inserting into stuff.database, from the content as "lostdata"

Which generally returns anywhere from 250-500 lines of "one" There is a datapoint that resides in one of several hundred other databases - its name is specified by the stuff.database, possibly the SAC Despite the possible options, there are only three or four unique databases in each result set as a result, what I am currently doing: 

  announcement @dbname VARCHAR (255) a_cursor cursor #tmptable Open local selection for the DISTINCT database from a_cursor @dbname while checking next do not fetch from a_cursor @@ FETCH_STATUS = 0 start set @sQL = 'update #tmptable set missingdata = bin.dataaggregate from (select Pd.id id, STUFF ((Select "',' '+ pdd.bin from server. [' + Dbname + '] .dbo.proddetails PDD where pdd.id = pd.id by pdd.id equals There, pdd.bin FOR XML PATH ( ''), type) KValue ( '' K '', '' VARCHAR (max) ''), 1, 1, 'server' '') dataaggregate. ['+ Dbname +] .dbo.proddetails PD) on bin insertion tir.id = bin.id EXEC sp_executesql #tmptable Join TIR @SQL FETCH before a console, dbname END close a_cursor DEALLOCATE a_cursor  < / Pre> 
 Since usually only a handful of databases are required in each setting result, the cursor has to loop only to some extent and the performance hit is not terrible. Even so, I do not like to use them and feel that there should be a more efficient way to do this. Any ideas?

Use this: 

  select #tmptable Stuff. Id, stuff.datapoint, stuff [Database], 'AS' disappearance, more than row_number ()) (as serial by stuff.id) declared by STUFF @i int = 1; Announced @max integer = (Selection of maximum (RN) from #tmptable), while @i

Comments

Post a Comment