Bulk copy of data from one column to another in SQL Server

Question

I want to copy the value of one column to another column in SQL Server. This operation needs to be carried out across the whole DB which has 200M rows. My query syntax is:

UPDATE [Net].[dbo].[LINK]
SET  [LINK_ERRORS] = [OLD_LINK_ERRORS]

However, very soon I exhaust the transaction log and the query aborts. What's the best way to initiate this in batches?

Thanks,

Answer 1

multiple updates might work.

update dbo.LINK
set LINK_ERRORS=OLD_LINK_ERRORS
where ID between 1 and 1000000

update dbo.LINK
set LINK_ERRORS=OLD_LINK_ERRORS
where ID between 1000001 and 2000000

etc...

Answer 2

Updating 200M rows is not a good idea.

You could either select all of the data into a new table and copy the LINK_ERRORS field in the SELECT,

select *, OLD_LINK_ERRORS as LINK_ERRORS into LINK_tmp from LINK
GO
exec sp_rename LINK, LINK_bkp
GO
exec sp_rename LINK_tmp, LINK
GO
drop table LINK_bkp

or if the next thing you're going to do is null out the original OLD_LINK_ERRORS column, you could do something like this:

sp_rename 'LINK.OLD_LINK_ERRORS', 'LINK_ERRORS', 'COLUMN'
GO
ALTER TABLE LINK ADD OLD_LINK_ERRORS <data type>
GO

Answer 3

I would consider doing this in SSIS where you can easily control the batch (transaction) size and take advantage of bulk operations SSIS provides. Of course, this may not work if you need a programmatic solution. This would be a very trivial SSIS operation.