I am going to perform a table-wide update on a huge table (+200Millon records) so I am going to populate the data via SELECT into a separate table, than drop the original and rename the new one.
In one of the articles someone mentioned that it is better to create the new table with the same name in a temporary schema (e.g. 'clone') and switch it to the used one (e.g. 'dbo'), than to use the original schema with a temporary name and call sp_rename after the data is in place and the old table is dropped.
I was looking into this, but I cannot think of anything why the schema switch is better than the sp_rename. Can anyone find any good reason why is better to use the first or the second approach?
EDIT: I want to update the values in a specific column
EDIT2: Ultimately my question is, if I decide to go down the way of creating a new table to transfer data to which alternative to use:
CREATE TABLE dbo.newTable ... DROP TABLE dbo.originalTable EXEC sp_rename N'dbo.newTable', N'dbo.originalTable'
CREATE TABLE clone.originalTable ... DROP TABLE dbo.originalTable ALTER SCHEMA dbo TRANSFER clone.originalTable