SQL RENAME TABLE

SQL RENAME TABLE Statement

The SQL RENAME TABLE statement is used to rename an existing table in a database. This command allows you to change the name of a table without affecting its structure or the data contained within it.

Syntax

RENAME TABLE old_table_name TO new_table_name;
  • RENAME TABLE: This is the SQL keyword used to rename a table.
  • old_table_name: This specifies the current name of the table you want to rename.
  • new_table_name: This specifies the new name you want to assign to the table.

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then renaming the table.

Step 1: Creating a Database

This step involves creating a new database where the table will be stored.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named employees within the previously created database.

USE example_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    hire_date DATE
);

Here, we define the employees table with columns for id, first_name, last_name, email, and hire_date. The id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the employees table.

INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('John', 'Doe', 'john.doe@example.com', '2023-01-01');
INSERT INTO employees (first_name, last_name, email, hire_date) VALUES ('Jane', 'Smith', 'jane.smith@example.com', '2023-02-01');

Here, we insert two rows of data into the employees table.

Step 4: Renaming the Table

This step involves renaming the employees table to staff.

RENAME TABLE employees TO staff;

This command will rename the employees table to staff, while retaining all its data and structure.

To verify the change, you can use the following command to describe the new table:

DESCRIBE staff;

This command will show the structure of the renamed staff table.