Updating an Entire Column in a 10 GB table

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Updating an Entire Column in a 10 GB table

13 posts, Page 2 of 2

««1 2

Updating an Entire Column in a 10 GB table

Rate Topic

Display Mode

Topic Options

Author

Message

Bhuvnesh

Posted Monday, December 31, 2012 4:06 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 7:03 AM
Points: 2,562, Visits: 3,453

chandan_jha18 (12/27/2012)

in my update statement, the condition is such that the column may get changed from 1 to 2 and next time when update happens that set of data with value '2' is going to get updated again.

batch approach always give leverage to handle the resources like memory , disk space and also we can schedule it for off-peak hours. and to handle your "adhoc updation hiccups" ,
1) you can set a update trigger so that you can catch the records which are being getting update during batch process
2) and save them in any temp table
3) once the batch got completed pick those values from that temp table and update the new column.
4) drop the temp table and trigger once the new column populated completely.

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1401352

ngreene

Posted Monday, December 31, 2012 5:56 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 8:35 AM
Points: 18, Visits: 265

Hope this helps

/*Example table to be updated (e.g. your 10GB table) **************/
--DROP TABLE table1
CREATE TABLE table1(tableID INT IDENTITY(1,1) Primary Key, field1 varchar(50),UpdateField varchar(50))
GO
INSERT INTO table1(field1)
SELECT newid()
GO 50
SELECT * FROM table1
/****************************************************/

/******explanation of my post***********/
IF OBJECT_ID('tempdb..#looptemp') IS NOT NULL
BEGIN
DROP TABLE #looptemp
END

CREATE TABLE #looptemp(pkid INT IDENTITY,tableid INT)--tableid will hold the tableid from the above table
INSERT INTO #looptemp(tableid)
SELECT tableid FROM table1

DECLARE @counter INT
DECLARE @tableid INT
SET @counter=1

WHILE @counter <= (SELECT MAX(pkid) FROM #looptemp)
BEGIN
SET @tableid=(SELECT tableid FROM #looptemp WHERE pkid = @counter)

--HERE IS THE UPDATE TO YOUR TABLE
--YOU SPECIFY THE CLUSTERED INDEX ON THE IDENTITY INT
--THIS SHOULD ONLY CAUSE A ROW LOCK
UPDATE table1
set UpdateField=getdate()
WHERE tableID=@tableid

SET @counter=@counter+1
END
/****************************************/
GO
SELECT * FROM table1

Post #1401368

chandan_jha18

Posted Monday, December 31, 2012 7:13 AM

SSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 7:57 AM
Points: 415, Visits: 1,689

Thanks NGreene. Will try that very soon and post the results of execution times.

Wish you and your family a very happy new year!!!

Regards
Chandan Jha

Post #1401383

« Prev Topic | Next Topic »

13 posts, Page 2 of 2

««1 2

Permissions