datetime century date into pad dynamic cursor money percent sp job isnumeric isdate over update
SQLUSA.com
SQL 2008 GRAND SLAM ON 49 CD
FREE TRIAL  CLICK HERE TO ORDER  SEARCH
SQL Server Training SQL 2005 Scripts SQL 2008 Articles
SQL JOBS News Format Developer
How to update 29 million rows of a table?

Execute the following T-SQL example scripts in Microsoft SQL Server Management Studio Query Editor to demonstrate large update in small batches with waitfor delay to prevent blocking.


-- T-SQL large update table
USE tempdb;
SELECT * INTO SOD
FROM AdventureWorks2008.Sales.SalesOrderDetail
GO
--(121317 row(s) affected)
 
-- SQL update in batches of 10,000
WHILE (2 > 1)
  BEGIN
    BEGIN TRANSACTION
    UPDATE TOP ( 10000 ) SOD
    SET    UnitPriceDiscount = 0.08,
           ModifiedDate = CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112))
    WHERE  ModifiedDate < CONVERT(DATETIME,CONVERT(CHAR(10),getdate(),112))
    
    IF @@ROWCOUNT = 0
      BEGIN
        COMMIT TRANSACTION
         BREAK
      END
    COMMIT TRANSACTION
    -- 1 second delay
    WAITFOR DELAY '00:00:01'
  END -- WHILE
GO
 
/* Messages
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(10000 row(s) affected)
 
(1317 row(s) affected)
 
(0 row(s) affected)
*/
-- Cleanup
DROP TABLE SOD
GO

------------

Related articles:

What is the recommended batch size for SqlBulkCopy?

Batch Update on a very huge table

How to Use Batch Size to Speed Mass Updates, Inserts and Deletes

UPDATE (Transact-SQL)

http://www.sqlusa.com/bestpractices/largeupdate/

 

Order SQL 2008 GRAND SLAM Today!
SQLUSA.com Home Page
SQL Server Training at www.sqlusa.com.
Microsoft SQL Server 2012 Training Videos at www.sqlusa.com.
SQL Server 2008 Video Training at www.sqlusa.com.
SQL Server 2005 Training Videos at www.sqlusa.com.
Accounting
Administrative
Advertising
Arts
Architecture
Banking
Business Intelligence
Career Jobs
Celebrity
Computer
Consulting
Customer Service
Education
Engineering
Entertainment
Entry Level
Executive
Federal
Finance
Government
Hardware
Healthcare
Hospital
Human Resources
Information Technology
Insurance
Internet
Job Openings
Laboratory
Law Enforcement
Legal
Logistics
Manufacturing
Marketing
Medical
Military
Nursing
Pharmaceutical
Physician
Public Relations
Publishing
Real Estate
Restaurant
Retail
Sales
Social Media
Software
SQL Database
Telecomm
Therapist
Training
Transportation
Truck Driver
Travel
Web
Work from Home

FREE SS SQL / BI OLAP Short Videos on YOUTUBE.com

Microsoft Community Contributor 2011 Microsoft Community Contributor 2012

Search SQLUSA FREE SQL Server Articles & FREE T-SQL Scripts

JOIN US ON TWITTER

Copyright 2005-2012, SMI Corp. All Rights Reserved.

SQL Server 2012 is a program product of Microsoft Corporation.
SQL Server 2008 is a program product of Microsoft Corporation.
SQL Server 2005 is a program product of Microsoft Corporation.
SQL Server 2000 is a program product of Microsoft Corporation.