Teradata RENAME TABLE

The RENAME TABLE command is used to rename an existing table name in Teradata.

In order to rename a table in Teradata, you must have DROP privileges on the existing table and CREATE TABLE privileges on the containing database or user.

Teradata RENAME TABLE syntax

The generic syntax of Teradata RENAME TABLE is as follows.

RENAME TABLE database_name.old_tbl_name to database_name.new_tbl_name;

Here,

  • First, specify the name of the old table name along with the database name after the RENAME TABLE keywords.
  • Finally, specify the name of the new table name which you want to keep.

Teradata RENAME TABLE example

To demonstrate the RENAME TABLE in Teradata, let’s create a table name students in the TUTORIALSBOOK database.

CREATE SET TABLE TUTORIALSBOOK.STUDENT, NO FALLBACK
   (roll_no     INTEGER,
    first_name   CHAR(25),
    last_name    CHAR(25),
    DOB          DATE FORMAT 'YYYY-MM-DD'
   )
UNIQUE PRIMARY INDEX (roll_no);

Next, insert some records into the table.

INSERT INTO TUTORIALSBOOK.STUDENT VALUES (01,'Sagar','Sharma','1990-12-04');
INSERT INTO TUTORIALSBOOK.STUDENT VALUES (02,'Shankar','Kumar','1995-06-05');
INSERT INTO TUTORIALSBOOK.STUDENT VALUES (03,'Ranjit','Bera','1990-12-09');

You can check the records inserted into the table using the SELECT statement.

SELECT * FROM TUTORIALSBOOK.STUDENT
ORDER BY 1;

Output:

SELECT * FROM TUTORIALSBOOK.STUDENT ORDER BY 1;

 *** Query completed. 3 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

    roll_no first_name                last_name                        DOB
----------- ------------------------- ------------------------- ----------
          1 Sagar                     Sharma                    1990-12-04
          2 Shankar                   Kumar                     1995-06-05
          3 Ranjit                    Bera                      1990-12-09

Now you want to rename the existing student table to student_details. For this, you can use the below statement.

RENAME TABLE TUTORIALSBOOK.STUDENT TO TUTORIALSBOOK.STUDENT_DETAILS;

Output:

RENAME TABLE TUTORIALSBOOK.STUDENT TO TUTORIALSBOOK.STUDENT_DETAILS;

 *** Table has been renamed.
 *** Total elapsed time was 1 second.

Next, check if the old table still exists by running the SELECT statement again.

SELECT * FROM TUTORIALSBOOK.STUDENT ORDER BY 1;
 *** Failure 3807 Object 'TUTORIALSBOOK.STUDENT' does not exist.
                Statement# 1, Info =0
 *** Total elapsed time was 1 second.

You can see that the old table does not exist anymore.

Finally, you can run the SELECT query on the new table and get the desired output.

SELECT * FROM TUTORIALSBOOK.STUDENT_DETAILS ORDER BY 1;

 *** Query completed. 3 rows found. 4 columns returned.
 *** Total elapsed time was 1 second.

    roll_no first_name                last_name                        DOB
----------- ------------------------- ------------------------- ----------
          1 Sagar                     Sharma                    1990-12-04
          2 Shankar                   Kumar                     1995-06-05
          3 Ranjit                    Bera                      1990-12-09

Please get connected & share!

Advertisement