Updating a table by looping through all the record

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

General SQL Server Forums

Script Library

Updating a table by looping through all the record

Author

Topic

magictech
Starting Member

44 Posts

Posted - 2004-10-01 : 11:10:36

The process of adding a column with DEFAULT (0) to a table that has 15million records takes a despicable amount of time (too much time) and the transaction log of the database grew to an unacceptable size. I would like to accomplish the same task using this procedure:

· Add the column to the table with null value.
· Loop through the table (500000 records at a time) and SET the value in the newly added column to 0.
· Issue a commit statement after each batch
· Issue a checkpoint statement after each batch.
· Alter the table and SET the column to NOT Null DEFAULT (0)

Here is my Sample script

ALTER TABLE EMPLOYEE ADD EZEVALUE NUMERIC (9,6) NULL
Go

Loop
UPDATE EMPLOYEE SET EZEVALUE = 0
Commit Tan
CHECKPOINT
END (Repeat the loop until the rows in EMPLOYEE have the value 0)

Go

ALTER TABLE EMPLOYEE ALTER COLUMN EZEVALUE NUMERIC (9,6) NOT NULL DEFAULT (0)

My problem is with the loop section of this script. How do I structure the loop section of this script to loop through the employee table and update the EZEVALUE column 500000 rows at a time, issue a Commit Tran and a CHECKPOINT statement until the whole table has been updated. Does anyone out there know how to accomplish this task? Any information would be greatly appreciated.

Thanks in advance

surefooted
Posting Yak Master

188 Posts

Posted - 2004-10-01 : 11:51:54

[code]
set rowcount 500000

Update Employee
Set Ezevalue = 0
Where Ezevalue is null
[/code]

Keep running the update you update all rows.

-Jon
Now a "Yak Posting Veteran".

magictech
Starting Member

44 Posts

Posted - 2004-10-01 : 12:31:20

Thanks for your responds. However, I would like the update statement to loop through the entire table updating 500000 rows at a time until the whole table has been updated. Is this possible? Please help.

Thanks in advance

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-01 : 13:32:52

did you try it? did you read about Update statements in books on-line?

SQL statements like UPDATE operate on sets of records at a time.

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-05 : 12:41:38

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 int)
GO

DECLARE @x int
SET @x = 0
WHILE @x < 100
BEGIN
INSERT INTO myTable99(Col2)
SELECT Null UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

SET @x = @x + 1
END

SELECT COUNT(*) FROM myTable99
SELECT @x = 1
SET ROWCOUNT 10

WHILE EXISTS (SELECT * FROM myTable99 WHERE Col2 IS NULL)
BEGIN
BEGIN TRAN
UPDATE myTable99
SET Col2 = 0
WHERE Col2 IS NULL
SELECT 'Iteration: ' + CONVERT(varchar(3),@x) + ' Rows Modified: ' + CONVERT(varchar(5),@@ROWCOUNT)
SELECT @x = @x + 1
COMMIT TRAN
END
GO

SET ROWCOUNT 0
SET NOCOUNT OFF
DROP TABLE myTable99
GO
[/code]

Brett

8-)

magictech
Starting Member

44 Posts

Posted - 2004-10-05 : 13:14:07

Thanks for your responds. Could you please explain to me what this script suppose to accomplish. I ran the script and I don't seems to understand what the script is doing. Thanks in advance.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources