HOME BLOG

Archive for April, 2022

Handy SQL query for backups

Posted on: April 13th, 2022 by Olu No Comments

Hi folks,

In this post I share a quick and handy technique for backing up tables within a database. It’s the CREATE TABLE AS statement.

You can use it to create a table by copying data from an existing table e.g.

CREATE TABLE new_table
  AS (SELECT * FROM old_table);

 

You can even back up specific columns or rows that meet certain conditions e.g.

CREATE TABLE suppliers
  AS (SELECT companies.id, companies.address, categories.cat_type
      FROM companies, categories
      WHERE companies.id = categories.id
      AND companies.id > 1000);

 

Note: this is a light-weight backup and shouldn’t be used a substitute for a full backup of the entire database.

That’s all for now. Till next time, happy software development.

 

Reference

SQL: CREATE TABLE AS Statement. https://www.techonthenet.com/sql/tables/create_table2.php