Non cursor based looping within a script.

• Paul Giles-Randall

  Mr or Mrs. 500

  Points: 542

  More actions

  March 31, 2016 at 7:42 am

  #306088

  I have a script that I am trying hard not to use a cursor for.

  The script will insert values into a table, but I have the following scenario.

  Lets say the table I wish to insert into has four columns.

  1. Id [INT]

  2. UserID [INT]

  3. RefID [INT]

  4. NewValue [VarChar(20)]

  What I wish to do is create a script whereby I can in effect do the following:

  INSERT INTO [Table] (UserID, RefID, NewValue) Values (@UserID, @RefID, 'Fixed message')

  And the script will use:

  SELECT @user-id = UID, @RefID = RefID From UserTable Where <Some condition>.

  Now the above is easy with a cursor, but with 1.9 million records to process it is estimated to take 2.5 days to complete.

  So I have looked into creating a temp table holding the base data and looping around this temporary table. It is quicker than a cursor, but not by much.

  There must be a better way of doing this.

• TheSQLGuru

  SSC Guru

  Points: 134017

  More actions

  March 31, 2016 at 7:54 am

  #1868848

  Your post doesn't make sense as written. Nothing you have explains any need to iterate. So, what, EXACTLY, are you doing with this output:

  SELECT @user-id = UID, @RefID = RefID From UserTable Where <Some condition>

  It sounds like you have some sproc, process, etc. you need to feed those variables and do so one at a time maybe??

  Best,
  Kevin G. Boles
  SQL Server Consultant
  SQL MVP 2007-2012
  TheSQLGuru on googles mail service

• mister.magoo

  SSC-Forever

  Points: 47174

  More actions

  March 31, 2016 at 7:56 am

  #1868850

  Why aren't you just doing this:

  INSERT INTO [Table] (UserID, RefID, NewValue)

  SELECT UID, RefID, 'Fixed message'

  From UserTable

  Where <Some condition>.

  MM
  

  ---

  
  select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

• Forum Etiquette: How to post Reporting Services problems
[/url]
• Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]
• How to Post Performance Problems - by Gail Shaw
[/url]