Copy data between tables / environments

Oracle

-- Into an existing table
INSERT INTO target_tbl (id, name) SELECT id, name FROM source_tbl WHERE status = 'ACTIVE';

-- By creating a new table (CTAS)
CREATE TABLE target_tbl AS SELECT * FROM source_tbl;

-- From a remote database via a database link
INSERT INTO target_tbl SELECT * FROM source_tbl@remote_db;

Within the same database, use INSERT ... SELECT or CREATE TABLE AS SELECT (CTAS). For copies between environments, a database link (@remote_db) or Data Pump is preferred. The APPEND hint speeds it up with direct-path.

PostgreSQL

-- Same database
INSERT INTO target_tbl (id, name) SELECT id, name FROM source_tbl;
CREATE TABLE target_tbl AS SELECT * FROM source_tbl;

-- Between environments (psql pipe)
-- pg_dump -t source_tbl sourcedb | psql targetdb
-- or read the remote table with postgres_fdw and INSERT

Within the same DB, use INSERT ... SELECT / CTAS. Between servers, use a pg_dump | psql pipeline or copy through a foreign table with postgres_fdw.

MySQL

-- Same database
INSERT INTO target_tbl (id, name) SELECT id, name FROM source_tbl;
CREATE TABLE target_tbl AS SELECT * FROM source_tbl;

-- Between servers (mysqldump pipe)
-- mysqldump sourcedb source_tbl | mysql -h targethost targetdb

Within the same DB, INSERT ... SELECT or CREATE TABLE ... AS SELECT. Between different servers, piping mysqldump output into the mysql client is common. CTAS does not copy indexes and constraints.

SQL Server

-- Into an existing table
INSERT INTO target_tbl (id, name) SELECT id, name FROM source_tbl;

-- By creating a new table
SELECT * INTO target_tbl FROM source_tbl;

-- Between servers: linked server
INSERT INTO target_tbl SELECT * FROM [REMOTE_SRV].shop.dbo.source_tbl;

In SQL Server, copying into a new table is done with SELECT ... INTO (the CTAS equivalent). Between servers, use a linked server (four-part name), bcp out/in or SSIS.

MongoDB

// Write an aggregation result to another collection
db.source_tbl.aggregate([
  { $match: { status: "ACTIVE" } },
  { $out: "target_tbl" }
]);

// Merge into an existing collection (upsert)
db.source_tbl.aggregate([ { $merge: { into: "target_tbl", whenMatched: "merge", whenNotMatched: "insert" } } ]);

$out replaces the target collection entirely; $merge merges with/inserts into existing documents (same or another DB). For moves between servers, use mongodump | mongorestore or mongoexport/mongoimport.

Redis

# Move a single key to another server (deleted from the source)
MIGRATE target_host 6379 customer:1 0 5000

# To copy (without deleting): DUMP + RESTORE
DUMP customer:1          # returns the serialized value
RESTORE customer:1new 0 "<dump-data>"

MIGRATE atomically moves the key to the target instance and deletes it from the source (with the COPY option it does not delete). To copy within the same instance, use the COPY command (6.0+) or DUMP+RESTORE.

Elasticsearch

# Within the same cluster
POST _reindex
{ "source": { "index": "source_tbl" }, "dest": { "index": "target_tbl" } }

# From a remote cluster (remote reindex; requires the whitelist setting)
POST _reindex
{ "source": { "remote": { "host": "http://remote:9200" }, "index": "source_tbl" },
  "dest": { "index": "target_tbl" } }

_reindex copies between indexes; it can be filtered with a query and transformed with a script. Between clusters, use remote reindex (reindex.remote.whitelist must be defined on the target). Large jobs are parallelized with slices.

Open in DBAtlas →