Duplicating tables

Sometimes we may need to copy the contents of one table to another, or part of one table to another. To do this we can use the CREATE TABLE statement combined with as ASĀ  and SELECT statements. The CREATE TABLE statement will be used to create the new table we plan to create. The AS keyword is used to combine the SELECT statement we need to select the field(s) we will be copying from the existing table. Below is the basic syntax to the command.

CREATE TABLE new_table_name AS
SELECT field_name(s)
FROM existing_table;

Lesson Activities

  1. Create a backup of the student table called student_backup selecting all field values
    Click to see code
    CREATE TABLE student_backup AS
    SELECT *
    FROM student;
  2. Create a backup of the book table called books_backup and only include the book_id, title, and price
    Click to see code
    CREATE TABLE books_backup AS
    SELECT book_id, title, price
    FROM books;

Questions