sql loop.. - 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 2000 Forums

SQL Server Development (2000)

sql loop..

Author

Topic

mrjack
Yak Posting Veteran

50 Posts

Posted - 2006-03-16 : 04:25:46

i have to update one table from another..and 12mil rows affected..and i already created a script to update the table..but the thing is..it stop half way and im thinkin of doing it by batch,maybe few set of records one time. can anyone help me on this?

UPDATE table1 ;
SET table1.column2= 'yes' ;
FROM table2 ;
WHERE table2.column1 = table1.column1;
AND table1.column2 IS NULL

and theres maybe a better design?

thanks alot guys..im new with this SQL...

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2006-03-16 : 05:25:55

Check out SET ROWCOUNT in Books Online:

Causes Microsoft® SQL Server™ to stop processing the query after the specified number of rows are returned.

Syntax
SET ROWCOUNT { number | @number_var }

Arguments
number | @number_var

Is the number (an integer) of rows to be processed before stopping the given query.

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-03-16 : 06:13:15

Just off the top of my head:

set rowcount 100000

while ((SELECT COUNT(*) FROM table1 WHERE column2 IS NULL) > 0)
  begin 
   UPDATE table1 ;
   SET table1.column2= 'yes' ;
   FROM table2 ;
   WHERE table2.column1 = table1.column1;
     AND table1.column2 IS NULL
   waitfor delay '00:00:05'
  end

You might get probles with the transactionlog here so you might have to back it up in the middle here somewhere...

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources