@SwapTables — Swaps the contents of one table for another
@SwapTables String table-name, String table-name
The @SwapTables system procedure swaps the contents of one table for another. So, for example, if table A has 2 rows and table B has 10 rows, after executing the following system procedure call table A would have 10 rows and table B would have 2 rows:
sqlcmd> exec @SwapTables 'A' 'B';
The tables being swapped must have identical schema. That is the names, datatype, and order of the columns must be the same and the tables must have the same indexes and other constraints. Also there cannot be any views on either table. If these requirements are not met, or if either of the named tables does not exist, the system procedure returns an error.
The system procedure provides a significant performance advantage over any comparable SQL statements when swapping large tables because the operation does not actually move any data. The pointers for the two tables are switched, eliminating any need for excessive temporary storage or data movement.
When using database replication (DR), the @SwapTables procedure is treated like a schema change and will pause replication. To use @SwapTables in a DR environment, follow the procedures for schema changes. That is:
When using passive DR:
Pause the master database with voltadmin pause --wait.
Invoke @SwapTables on the master database.
Resume the master database with voltadmin resume.
Invoke the same @SwapTables call on the replica.
When using XDCR:
Pause all of the clusters in the XDCR relationshp with voltadmin pause --wait.
Invoke the same @SwapTables call on all of the databases.
Resume all the databases with voltadmin resume.
Returns one VoltTable with one row and one column.
|MODIFIED_TUPLES||BIGINT||The number of tuples affected by the swap. In other words, the sum of the tuples in both tables.|