November 10, 2006 at 3:58 am
Hi All,
I have a simple query and I would like to use Rowcount to do abtch updates, the problem I am having now is that the update happens only once.
Set @sql = '
SET ROWCOUNT 50000
Update tmp
Set tmp.Profiled = ''Y''
from TMP tmp
inner join dbo.TB_PersonProfile MRT
on tmp.route = MRT.personurn
iNNER JOIN CompanyPRofile CP
on mrt.name = CP.name
where CP.operator = ''DLL''
SET ROWCOUNT 0
'
Can anyone help please.
Thanks
November 10, 2006 at 6:16 am
That doesn't need to be dynamic sql at all. This works perfectly :
SET ROWCOUNT 10000
WHILE 1=1 --Be careful with this you don't want an infinte loop!
BEGIN
UPDATE myTab
SET myCol = @newVal
SET Updated = 1
WHERE mycol <> @newVal AND Updated = 0
IF @@ROWCOUNT = 0
BREAK
waitfor delay 00:00:02 --wait 2 seconds before running again
END
SET ROWCOUNT = 0
November 10, 2006 at 6:17 am
Or maybe this could eliminated a costly join :
create table ttemp
(
id primary key clustered
)
insert into ttemp (id) select id from tmain
set rowcount 10000
while @@rowcount > 0
begin
update tmain set col = 'wathever'
from tmain inner join ttemp on tmain.id = ttemp.id
delete from ttemp
--would have to check to make sure this process updates and delete matching rows too!!
end
set rowcount 0
--check if new rows where inserted and update if needed... or set a new rule/default beforehand
drop table ttemp
December 1, 2006 at 8:49 am
December 1, 2006 at 9:24 am
Very simple way :
Add a column IsUpdated
SET rowcount 50
while @@rowcount > 0
begin
update dbo.table set IsUpdated = 1, YourCol = 'Whatever' WHERE IsUpdated = 0
end
SET rowcount 0
December 14, 2006 at 5:16 pm
Hi
I ran a test using one of the code displayed above, but I was inserting into a table using rowcount, and it just kept inserting over and over again without stopping.
Table1 = 10m rows
Table2 = empty
I want to insert into Table2 in abtches of 10000, I used the idea above and it just kept inserting and inserting like an indefinite loop
December 14, 2006 at 6:36 pm
Can't be sure without the code, but I'm guessing that you are not updating a flag column, nor are you keeping the last record inserted. Both which would allow you to break the loop eventually.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply