Strata Scratch Problem Sets

Problem sets, exercises, and educational guides for SQL and python. These exercises are specifically tailored for business and marketing analytics students and novices.

Back to the Strata Scratch website
Login to Strata Scratch

How to Copy Data from One Table to Another

Copying or moving data from one place to another is one of the most common activities you will have to do when creating your database. This tutorial will cover the basics you need to learn in order to start copying fields of data to another table.

Strata scratch uses PostgreSQL dialect for database programming. If you are a beginner with minimal background in coding, then this easy-to-follow guide is the right page to start manipulating fields of data in your database.

Copying data from an existing table requires you to type the following statement into the editor:

strata scratch

The CREATE TABLE AS statement allows you to create a new table in your database. To name your table, simply follow the syntax schema.table_name, where schema is the name of your private schema and table_name is the name you want to assign to your new table.

The SELECT statement is used to retrieve data from an existing table. You can follow the syntax SELECT * FROM schema.existing_table if you want to retrieve all the values available in the field. This method is very useful if you want to create a backup file for your table.

Copying Data from an Existing Table

strata scratch

strata scratch

strata scratch

Viewing the New Table

strata scratch

strata scratch

Copying Data with a Condition Statement

If you want to copy only a particular set of data from an existing table, you can add a condition statement by using the WHERE clause. The WHERE clause allows you to copy only the data you wanted from an existing table to your new table.

The syntax of the SELECT statement with the WHERE clause is as follows:

strata scratch

You can specify a search condition to filter out rows you do not need to copy by using comparison or logical operators such as >, <, and = operator.

For a demonstration, consider the table below named airbnb with the following records:

strata scratch

We are interested to create a new table and copy only the fields with more than 9 searches. Let us name our new table as airbnb_searches.

strata scratch

strata scratch

strata scratch

Now you have learned the basics on how to copy data from an existing table to a new table. You should be able to apply the syntax covered in this tutorial to start copying rows of data from one table to another.