Swap table name in MySQL, SQL Server

Swap table name in MySQL, SQL Server

Sometimes we need to swap table names of existing two tables. We can do this using a single sql statement in MySQL.

Suppose we have two tables SalesOrder, SalesOrderDetails. Here is the description of these tables

DESCRIBE SalesOrder;

SalesOrder

‘SaleOrderNo’,

‘ItemNo’,

‘Qty’,

‘UnitPrice’

DESCRIBE SalesOrderDetails;

SalesOrderDetails

‘SalesOrderNo’,

‘Date’,

‘CustomerNo’,

‘ClerksNo’

So we want to swap name of these two tables.

The statement is:

RENAME TABLE SalesOrder TO tmp,

SalesOrderDetails TO SalesOrder,

tmp TO SalesOrderDetails;

DESCRIBE SalesOrderDetails;

SalesOrderDetails

‘SaleOrderNo’,

‘ItemNo’,

‘Qty’,

‘UnitPrice’

DESCRIBE SalesOrder;

SalesOrder

‘SalesOrderNo’,

‘Date’,

‘CustomerNo’,

‘ClerksNo’

In SQL Server this can be done by using the following statements,

USE SalesOrder

GO

EXEC sp_rename SalesOrder, tmp

GO

EXEC sp_rename SalesOrderDetails, SalesOrder

GO

EXEC sp_rename tmp, SalesOrderDetails

GO