Prev: Search and Replace in a column
Next: About Autonumbering
From: Erland Sommarskog on 17 Feb 2010 02:27
(Shriram Chandrasekaran) writes:
> Thanks for ur suggesstion. i am having 2 million records in my
> database.while i try to update a column for all the records,it takes
> very very long time.
>
> How to specify ranges with batches while updating a column?

SELECT @first = MIN(somecol) FROM tbl
WHILE @first IS NOT NULL
BEGIN
UPDATE tbl
SET col = @value
WHERE somecol BETWEEN @first AND @first + 10000

SELECT @first = MIN(somecol)
FROM tbl
WHERE somecol > @first + 10000
END

somecol should not just be any column, but the leading column in your
clustetered index.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Search and Replace in a column
Next: About Autonumbering