Clever Geek Handbook

UPDATE for a large table with one value

I have a table with 2 billion rows . The recovery mode is simple.

I have a simple task:

UPDATE myTable SET columnA = 'X'

At first the task seems simple, but everywhere I read that it is better to do SELECT INTO to another table or to perform a batch update.

Is a package update required? Will the query above blow up the transaction log?

If I create another table, I will most likely run out of free space.

Any help would be appreciated.

EDIT: I don't have a primary key. Also, no indexes, no limits, nothing.

Perhaps this will help?

SET rowcount 10000
Update myTable 
set columnA ='X'
where columnA <> 'X'

while @@rowcount>0

BEGIN
SET rowcount 10000
Update myTable 
set columnA ='X'
where columnA <> 'X'

end

SET rowcount 0
+3


source to share


4 answers


Run it in batches



 Declare @pk integer = 0
 declare @max integer
 select @max = Max(primKeyCol) from myTable
 While @pk < @Max Begin
      Update myTable set columnA = 'X'
      Where primKeyCol Between @pk and @pk + 999
      Set @pk += 1000
 End
0


source


I suggest you use batch update because this kind of UPDATE on a large table can cause a lot of locks. I found a good example of packages here



0


source


Try to update 50,000 rows at a time

DECLARE @chk INT = 1
WHILE @chk > 0
BEGIN
  ;WITH CTE as
  (
    SELECT TOP 50000 columnA
    FROM myTable
    -- the reason for this check is to check for NULLS in columnA
    -- otherwise it could just be columnA <> 'X'
    WHERE exists(SELECT columnA EXCEPT SELECT 'X')
  )
  UPDATE CTE SET columnA = 'X'
  SET @chk = @@rowcount
  WAITFOR DELAY '00:00:30' -- 30 seconds, may need to change it
END

From microsoft Link

Using SET ROWCOUNT will not affect DELETE, INSERT and UPDATE in a future version of SQL Server. Avoid using SET ROWCOUNT with DELETE, INSERT, and UPDATE statements in new developments, and plan to modify applications that currently use it. For similar behavior, use the TOP syntax. See TOP (Transact-SQL) for more information.

0


source


This is a pretty wild non-sql

way, but you can use tools ETL

(for example SSIS

) to avoid significant registration.

Disclaimer: This can be a time consuming step and can take up file space on your server.

You have two data flow tasks (DFT1, DFT2).

DFT1

enter image description here

Using the Derived Column transformation, pass the value "A" instead of the actual value columnA

.

The flat file will now have the required data. The next step would be to import this into a table.

Outside the DFT, crop this table now. TRUNCATE

as you probably know, doesn't work. Therefore, your transaction log will not be affected.

Finally,

DFT2

Import data to tableA from a flat file.

enter image description here

SSIS

does minimal logging internally, so again the trasaction log won't bother too much.

Also, I just noticed that you added the ssis tag to your question, so this answer should be valid.

0


source






More articles:


All Articles