March 14, 2014 at 12:23 pm
I have around 2 million records which needs to be updated. Any suggestions to make this faster would be appreciated.
Declare @a [varchar](9),
@b-2 [integer],
@SQLexec [varchar](500)
Set @b-2 = 0
Declare Cur1 Cursor
For Select A From dbo.tblname
Open Cur1
Fetch from Cur1 into @a
WHILE @@FETCH_STATUS = 0
Begin
Set @SQLexec = 'Update dbo.tblname Set rec_num = '+Cast(@B as varchar)+' from dbo.tblname where A = '+@A
Exec(@SQLexec)
Fetch Next From Cur1 into @a
End
Close Cur1
DEALLOCATE Cur
GO
March 14, 2014 at 12:31 pm
You posted in 2 different forums for 2 different versions, please define which one would you like to consider. The other post is this one: http://www.sqlservercentral.com/Forums/Topic1551279-65-1.aspx
Your code seems to do something as simple as this:
Update dbo.tblname Set
rec_num = 0
If it doesn't satisfy your requirements, check the code you posted.
March 14, 2014 at 12:36 pm
Thanks, please consider this one.
I am newbee on this.
What I am trying to do is set @rec_num to 0 and then
Set @Nrec_num = @Nrec_num + 1
Set @SQLexec = 'Update dbo.tblname Set rec_num = '+Cast(@Nrec_num as varchar)+' from dbo.voters where part_no = '+@part_no
Exec(@SQLexec)
Can you please help me with this?
March 14, 2014 at 12:41 pm
Pritz (3/14/2014)
Thanks, please consider this one.I am newbee on this.
What I am trying to do is set @rec_num to 0 and then
Set @Nrec_num = @Nrec_num + 1
Set @SQLexec = 'Update dbo.voters Set rec_num = '+Cast(@Nrec_num as varchar)+' from dbo.voters where part_no = '+@part_no
Exec(@SQLexec)
Can you please help me with this?
Same concepts as Luis posted previously. Drop the loop and there is absolutely no need to use dynamic sql for this. Probably need ROW_NUMBER for this one but without ddl and sample data it is just guesswork.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 14, 2014 at 12:44 pm
Something like this:
with basedata as (
select
*, rn = row_number() over (order by part_no)
from dbo.voters
)
update basedata set
rec_num = rn;
Edit: Removed extra order by in CTE
March 14, 2014 at 12:46 pm
Noting that you're on 2005 (as you noted in the other thread), you could use the DENSE_RANK function.
Update dbo.voters
Set rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters
with basedata as (
select
*, rn = DENSE_RANK() over (order by part_no)
from dbo.voters
)
update basedata set
rec_num = rn;
I might be wrong, but you could help us to avoid guessing if you posted some sample data and expected results as explained on the article linked in my signature.
EDIT: Code correction
March 14, 2014 at 12:58 pm
Luis Cazares (3/14/2014)
Noting that you're on 2005 (as you noted in the other thread), you could use the DENSE_RANK function.
Update dbo.voters
Set rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters
I might be wrong, but you could help us to avoid guessing if you posted some sample data and expected results as explained on the article linked in my signature.
get the following message when running your code above:
Msg 4108, Level 15, State 1, Line 11
Windowed functions can only appear in the SELECT or ORDER BY clauses.
March 14, 2014 at 1:03 pm
Fixed my code posted earlier.
March 14, 2014 at 1:03 pm
got this error:
Windowed functions can only appear in the SELECT or ORDER BY clauses.
March 14, 2014 at 1:07 pm
Pritz (3/14/2014)
got this error:Windowed functions can only appear in the SELECT or ORDER BY clauses.
First, for what code. Posting "got this error" really doesn't tell anyone anything.
Second, recheck the previous posts, you will find I corrected my code.
March 14, 2014 at 1:10 pm
Update dbo.voters
Set rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters
Got the error for the script above.
can you re-post the corrected one? I cannot see it.
Thanks,
March 14, 2014 at 1:14 pm
Pritz (3/14/2014)
Update dbo.votersSet rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters
Got the error for the script above.
can you re-post the corrected one? I cannot see it.
Thanks,
Funny, I scroll back up to my earlier posts and see my code no problem.
March 14, 2014 at 1:19 pm
Pritz (3/14/2014)
Update dbo.votersSet rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters
Got the error for the script above.
can you re-post the corrected one? I cannot see it.
Thanks,
I could repost it with no problem, but Lynn's code should work fine. You need to review which ranking function will work for you as we still haven't got sample data.
March 14, 2014 at 1:28 pm
Luis Cazares (3/14/2014)
Pritz (3/14/2014)
Update dbo.votersSet rec_num = DENSE_RANK() OVER( ORDER BY part_no)
from dbo.voters
Got the error for the script above.
can you re-post the corrected one? I cannot see it.
Thanks,
I could repost it with no problem, but Lynn's code should work fine. You need to review which ranking function will work for you as we still haven't got sample data.
I have to agree, we are making some assumptions here. My assumption is that you are trying to number each row sequentially regardless of uniqueness of the part_no values.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply