August 8, 2010 at 6:25 am
I am using the following code to run the update in batched so that it wont take full log space,please verify if this is really doing transactions in batches or not ? I doubt because it is taking too much log space than expected.
declare @start int
declare @end int
set @start=1
set @end=100000
while @start<@end
BEGIN
begin transaction test
Update dbo.Employee
set jobcode = 1 Where jobcode <> 1
commit transaction test
set @start = @start + 100000
END;
August 8, 2010 at 7:04 am
Tara-1044200 (8/8/2010)
I am using the following code to run the update in batched so that it wont take full log space,please verify if this is really doing transactions in batches or not ? I doubt because it is taking too much log space than expected.declare @start int
declare @end int
set @start=1
set @end=100000
while @start<@end
BEGIN
begin transaction test
Update dbo.Employee
set jobcode = 1 Where jobcode <> 1
commit transaction test
set @start = @start + 100000
END;
No, it's not updating in batches. You might want to look in BOL at the update statement.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 8, 2010 at 7:09 am
could you help me to update in batches please.
August 8, 2010 at 9:24 am
Tara-1044200 (8/8/2010)
could you help me to update in batches please.
Try this link
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 8, 2010 at 9:48 pm
Tara-1044200 (8/8/2010)
I am using the following code to run the update in batched so that it wont take full log space,please verify if this is really doing transactions in batches or not ? I doubt because it is taking too much log space than expected.declare @start int
declare @end int
set @start=1
set @end=100000
while @start<@end
BEGIN
begin transaction test
Update dbo.Employee
set jobcode = 1 Where jobcode <> 1
commit transaction test
set @start = @start + 100000
END;
I assume you are trying to update in batches of 100000? They way you were doing it you were updating all the Emplyee records multiple times.
The following are missing from your code:
1) Control on how many Employee records are being updated
2) Control of which Employee record is being updated.
You will need to do something like this:
declare @start int
declare @end int
decalre @MaxEmpNumber int
select @MaxEmpNumber = max(EmplyeeNumber) from Employee
set @start=1
set @end=100000
While @Start <= @MaxEmpNumber
BEGIN
begin transaction test
Update dbo.Employee
set jobcode = 1 Where jobcode <> 1
where EmplyeeNumber >= @Start and EmplyeeNumber < @end
set @start = @start + 100000
set @end = @start + 100000
commit transaction test
END;
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
August 9, 2010 at 1:31 am
As explained by the previous poster, the update statement is actually updating all the rows of the table because there is not filtering clause (where clause) or top clause.
If you just wanted to update some column value based on the where condition on the same column then you can use the following code.
DECLARE @BatchSize INT, @Counter INT
SELECT@BatchSize = 10000,
@Counter = 1
WHILE ( 1 = 1 )
BEGIN
PRINT 'Updating next ' + CONVERT( VARCHAR(10), @BatchSize ) + ' records starting at record no. ' + CONVERT( VARCHAR(10), @Counter )
UPDATE TOP( @BatchSize ) dbo.Employee SET jobcode = 1 WHERE jobcode != 1
IF ( @@ROWCOUNT != @BatchSize )
BREAK
SELECT@Counter = @Counter + @BatchSize
END
[
--Ramesh
August 10, 2010 at 6:54 am
How about this..
DECLARE @rowcount int
SET @rowcount = 100000
SET rowcount 100000
WHILE @rowcount >0
BEGIN
BEGIN TRAN
Update dbo.Employee set jobcode = 1 Where jobcode <> 1
SET @rowcount = @@rowcount
COMMIT TRAN
END
and wondering what is the maximum number we can do in a batch? I would like to do 1 million as i have to update a total of 300 million.
August 10, 2010 at 10:29 am
Tara-1044200 (8/10/2010)
How about this..DECLARE @rowcount int
SET @rowcount = 100000
SET rowcount 100000
WHILE @rowcount >0
BEGIN
BEGIN TRAN
Update dbo.Employee set jobcode = 1 Where jobcode <> 1
SET @rowcount = @@rowcount
COMMIT TRAN
END
and wondering what is the maximum number we can do in a batch? I would like to do 1 million as i have to update a total of 300 million.
You might want to check out this article[/url] by a guy that should be an MVP. While the article talks about deleting, most of it will be appropriate for the update for minimizing the size of your transaction log.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply