Loops with SRN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Loops with SRN

Author

Topic

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-07 : 11:53:22

Hi

I rarely use cursors, as I've read how they take a lot of system resources when running on large data sets. Therefore I usually write some script to loop.

Usually I would add an SRN field to the table for my script to scroll through one at a time.
i.e. Alter table Table1 add SRN numeric identity(1,1)


declare @sqlstring nvarchar(4000),
		@Counter int,
		@Max int,
		@Variable_Test int

set @Max = (select count(*) from Table1)
set @Counter = 1

While @Counter <= @Max
	Begin
		set @Variable_Test = (SELECT Field5 from Table1 where SRN = @Counter)
		Set @sqlstring = 'update Table1 set Field6 = '' + @Variable_Test + '''
		set @Counter = @Counter + 1
     End

Apolgies for the made up sample, but my question is, how can I get my loop script working without the need of a SRN (Sequential Reference Number) to go through each record at a a time and update.

I know a cursor utilises @@RowCount, but can this (or a similiar method) be used with a loop without the need for an SRN? If so, how?

I want to avoid adding an SRN (as it will take addtional time on a large data set i.e. 10 million records, and avoid the need for cursors) - OR if you think cursors would be more appropriate for this, then please let me know why.

Many thanks!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-01-07 : 12:05:18

More than likely, you can do what you need to do with a simple update statement, so there is no need to do a loop or a cursor.

CODO ERGO SUM

dnf999
Constraint Violating Yak Guru

253 Posts

Posted - 2008-01-07 : 12:29:43

Apologies the loop is not really what i intend to do with the data, it was a bad example.

My main query is how can I incrementally loop through rows without creating an SRN/ID field and without using a cursor?

Thanks

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-07 : 12:33:21

Just to clarify the evils of cursors are not necessarily cursors themselves but iteration based processing in general.

Refer to the following article for more info:
[url]http://sqlblog.com/blogs/hugo_kornelis/archive/2007/11/28/poor-men-see-sharp-ndash-more-cursor-optimization.aspx[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources