How to Copy a Table Between Databases in BigQuery

Galaxy Glossary

How do I copy a table between two BigQuery databases?

Copies an entire table from one BigQuery project/dataset to another without losing schema or data.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why copy tables between BigQuery databases?

Backing up production data, migrating workloads, and sharing curated datasets all require moving tables across projects or regions.

What are the main copy methods?

Use SQL CREATE TABLE AS SELECT (CTAS), the bq cp CLI, or the Cloud Console UI. CTAS allows transformations; bq cp does a byte-wise clone.

When should I use SQL CTAS?

Choose CTAS to filter rows, mask PII, or change column names. The destination project pays for the query.

SQL CTAS steps

1. Open BigQuery editor. 2. Reference destination table. 3. SELECT from source. 4. Run.

How to copy `Customers` across projects?

The CTAS statement below clones every row from analytics_us.sales_customers to reporting_eu.sales_customers_archive.

CREATE TABLE `reporting_eu.sales_customers_archive` AS SELECT * FROM `analytics_us.sales_customers`;

How to copy with the CLI?

bq cp is faster and cheaper for 1-to-1 copies because it avoids a scan.

bq cp \ --project_id=analytics-us \ --location=US \ analytics_us.sales_orders \ reporting-eu:sales.orders_backup

Best practices

Set expiration on the destination, copy during off-peak hours, and validate row counts with bq show --schema.

Why How to Copy a Table Between Databases in BigQuery is important

How to Copy a Table Between Databases in BigQuery Example Usage


CREATE TABLE `reports_eu.orders_2023_backup`
OPTIONS(description='Year 2023 orders backup') AS
SELECT *
FROM `shop_prod.orders`
WHERE EXTRACT(YEAR FROM order_date)=2023;

How to Copy a Table Between Databases in BigQuery Syntax


-- SQL CTAS (transform or copy)
CREATE TABLE [IF NOT EXISTS] `target_project.target_dataset.target_table`
OPTIONS(
    expiration_timestamp=TIMESTAMP 'YYYY-MM-DD HH:MI:SS',
    description='string')
AS
SELECT [column_list]
FROM `source_project.source_dataset.source_table`
WHERE [condition];

-- bq CLI byte-wise copy (fastest)
bq cp \
[--project_id=SOURCE_PROJECT] \
[--location=LOCATION] \
[--time_partitioning_type=DAY|HOUR] \
[--label=KEY:VALUE] \
SOURCE_PROJECT:SOURCE_DATASET.SOURCE_TABLE \
DEST_PROJECT:DEST_DATASET.DEST_TABLE

Common Mistakes

Using CTAS for a simple 1:1 copy. This triggers a full table read, incurring query costs. Fix: use the <code>bq cp</code> command to perform a free, byte-wise clone.
Omitting backticks around fully-qualified table names. This causes unrecognized name errors. Fix: wrap identifiers like `project.dataset.table` in backticks.

Frequently Asked Questions (FAQs)

Does `bq cp` keep table partitions?

Yes. The command copies partitions, clustering, and labels without modification.

Can I overwrite an existing table?

Add --force to bq cp or use CREATE OR REPLACE TABLE in CTAS to replace the destination table.

Is cross-region copy supported?

No. You must first export to Cloud Storage or use BigQuery Data Transfer Service when regions differ.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.