@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), you must pause the database before using the @SwapTables procedure, or the system procedure returns an error. The process for using @SwapTables in an XDCR environment is the following:
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.