How to Copy Data from One Table to Another Table in Snowflake

As your Snowflake data warehouse grows, you’ll inevitably need to duplicate or move data between tables – whether for backups, testing, or building data pipelines. Snowflake provides several powerful methods to copy data from one table to another quickly and easily.

This guide walks you through using Snowflake SQL commands and best practices to copy data between tables like a pro.

Method 1: Snowflake Commands

Snowflake provides a set of SQL commands to copy data between tables. Here are the three main commands you’ll use:

INSERT INTO Command

The INSERT INTO command is a standard way to copy data from one table to another. Here’s the basic syntax:

INSERT INTO table_name (column1, column2)

SELECT column1, column2

FROM other_table;

This command selects data from other_table and inserts it into table_name. You specify the columns to copy in the INSERT INTO and SELECT clauses.

INSERT INTO is handy for:

• Copying specific columns between tables
• Transforming data during the copy (e.g. applying functions)
• Inserting into an existing table

SELECT INTO Command

SELECT INTO creates a new table and copies data into it in one step. The syntax in Snowflake is:

SELECT *

INTO new_table

FROM old_table;

This creates new_table with the same schema as old_table and copies all rows and columns.

Advantages of SELECT INTO:

• Quick way to duplicate an entire table
• Automatically handles creating the new table and schema
• Can apply filters and transformations in the SELECT clause

COPY INTO Command

COPY INTO loads data from staged files into a table. While it’s mainly used for loading external data, you can also use it to copy data between Snowflake tables.

COPY INTO target_table

FROM @my_stage/data_file.csv

FILE_FORMAT = (TYPE = CSV);

This example loads data from a CSV file in a Snowflake stage into target_table.

COPY INTO is useful when:

• Source data is in staged files (e.g. from an external source)
• Files are in a format like CSV, JSON, XML
• You want to apply transformations during the load

Method 2: No-code with Coefficient

Coefficient acts as a flexible, user-friendly bridge between Snowflake and your spreadsheet, making it easy to import, manipulate, and copy data between tables without writing any SQL.

It’s a game-changer for SQL-savvy analysts and business users alike.

Step 1. Install Coefficeint

Before getting started, install Coefficient for your spreadsheet. It only takes a few seconds, and you only have to do it once.

Step 2: Connect to Snowflake

Open the Coefficient Sidebar. Click on the menu icon and select “Connected Sources.”

Search for Snowflake and click “Connect”.

Enter your account credentials (Snowflake Account Name, Database Name, Username, Password, and Warehouse Name) to complete the connection.

Step 3. Import Data from Snowflake to Your Spreadsheet

Open the Coefficient sidebar. Click on the “Import from” button and select “Snowflake”.

Choose the tables and columns you want to import using either the visual query builder or a custom SQL query.

Perform the import to bring your data from Snowflake into Google Sheets.

Step 4. Modify Data as Needed

Make any necessary changes to the data in your spreadsheet. This could include data manipulation, transformations, or simply preparing the data in the required format.

Step 5. Export Data Back to Snowflake

In the Coefficient sidebar, click on the “Export to” button and select “Snowflake”.

Choose the tab and header row that contains the data to be exported.

Designate the target table in Snowflake where the data should be copied. Make sure you select the appropriate action from the list (e.g., Insert, Update, or Delete Fields).

Complete the field mappings for this export by mapping the spreadsheet columns to the corresponding fields in the target Snowflake table.

Confirm your settings and select the rows in your spreadsheet that you need to export to Snowflake.

Execute the export to copy data from your spreadsheet back into Snowflake.

Tips and Tricks for Copying Data Between Tables

When duplicating large datasets, optimizing performance is critical. Here are some pro techniques to copy data in Snowflake at lightning speed:

Use Bulk Inserts

Inserting data in bulk is significantly faster than single-row inserts. To load data en masse:

• Use INSERT INTO with a SELECT statement to copy multiple rows at once
• Leverage COPY INTO to efficiently load data from staged files
• Make the most of Snowflake’s columnar storage to avoid rewriting entire rows

Copy Data Instantly with Zero-Copy Cloning

Snowflake’s zero-copy cloning enables you to duplicate tables, schemas, or even entire databases almost instantly, with minimal storage costs. Cloning saves time by creating a copy nearly instantaneously using pointers to the original data.

To clone a table:

CREATE TABLE new_table

CLONE original_table;

This creates new_table as a copy of original_table using zero-copy cloning. Cloning is ideal for:

• Creating instant backups or restore points
• Duplicating data for testing or reporting
• Quickly spinning up new environments

Simplify Cross-Region & Cross-Cloud Copying

Snowflake’s replication and data sharing features allow you to efficiently copy and synchronize data across regions and clouds.

With a replication group, you can:

• Copy databases across multiple Snowflake accounts and regions
• Keep replicated data in sync with automatic updates
• Implement global data sharing with secure, real-time access

Meanwhile, data sharing enables you to grant read-only access to live data across Snowflake accounts, without the need to copy the actual data. This is perfect for sharing data with external partners or different departments.

Copying Data Between Snowflake Tables: Use Cases

Data Migration and Backup

One common reason to copy data is when you want to move it from one table to another. This might be part of a data migration process or to create a backup. Here’s how you can do this using Snowflake SQL commands:

1. Create a new table with the structure you want.
2. Use the INSERT INTO command to copy data from the old table to the new one.
3. Check that the data was copied correctly.
4. Delete the old table and rename the new one to replace it.

— Create new table

CREATE TABLE new_users (

  id INT,

  name STRING,

  email STRING

);

— Copy data from old table

INSERT INTO new_users (id, name, email)

SELECT id, name, email

FROM old_users;

— Check data

SELECT COUNT(*) FROM new_users;

— Delete old table

DROP TABLE old_users;

— Rename new table

ALTER TABLE new_users RENAME TO users;

Data Integration and Transformation

Copying data is also important when you need to combine and transform data from multiple sources. When integrating data, keep these best practices in mind:

• Use the same structure across tables to ensure they work together.
• Standardize data formats, units, and conventions to keep data consistent.
• Check data quality and integrity after loading to catch any problems early.
• Automate data copying using scheduled tasks or ELT (Extract, Load, Transform) tools to make workflows easier.

Snowflake’s SQL commands like INSERT INTO and SELECT INTO make it easy to integrate and transform data from various sources into a single view.

Data Warehousing and Schema Management

In data warehousing, copying data and schemas happens often when creating and managing different layers of the warehouse, such as staging areas, operational data stores (ODS), and data marts. Here’s how you can copy a schema and its data:

1. Use the CREATE SCHEMA command to create the new schema.
2. For each table in the original schema:
   • Use CREATE TABLE LIKE to copy the table structure in the new schema.
   • Copy data into the new table using INSERT INTO or SELECT INTO.

1. Check that the schema and data were copied correctly.

— Create new schema

CREATE SCHEMA staging;

— Duplicate table structures

CREATE TABLE staging.users LIKE prod.users;

CREATE TABLE staging.orders LIKE prod.orders;

— Copy data

INSERT INTO staging.users SELECT * FROM prod.users;

INSERT INTO staging.orders SELECT * FROM prod.orders;

— Validate

SELECT COUNT(*) FROM staging.users;

SELECT COUNT(*) FROM staging.orders;

Simplify Data Copying with Coefficient and Snowflake

By using Snowflake’s built-in commands, you can quickly and efficiently copy data between tables.

Whether you’re ensuring data redundancy, optimizing performance, or simplifying complex tasks like cross-region copying, Snowflake provides the tools required for various data copy needs.

 But if you’re looking for an easier, no-code solution, Coefficient can bridge the gap between your spreadsheets and Snowflake. Want an easier way to manage your data?

Try Coefficient today and make your tasks simpler.