Copiar datos entre tablas / entornos

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;

Dentro de la misma base de datos, use INSERT ... SELECT o CREATE TABLE AS SELECT (CTAS). Para copias entre entornos se prefiere un database link (@remote_db) o Data Pump. El hint APPEND lo acelera con 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

Dentro de la misma BD, use INSERT ... SELECT / CTAS. Entre servidores, use un pipeline pg_dump | psql o copie a través de una foreign table con 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

Dentro de la misma BD, INSERT ... SELECT o CREATE TABLE ... AS SELECT. Entre servidores distintos es habitual canalizar la salida de mysqldump hacia el cliente mysql. CTAS no copia índices ni restricciones.

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;

En SQL Server, la copia a una tabla nueva se hace con SELECT ... INTO (el equivalente de CTAS). Entre servidores, use un linked server (nombre de cuatro partes), bcp out/in o 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 reemplaza por completo la colección de destino; $merge fusiona con documentos existentes o los inserta (en la misma u otra BD). Para mover datos entre servidores, use mongodump | mongorestore o 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 mueve la clave de forma atómica a la instancia de destino y la elimina del origen (con la opción COPY no la elimina). Para copiar dentro de la misma instancia, use el comando COPY (6.0+) o 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 copia entre índices; se puede filtrar con una consulta y transformar con un script. Entre clústeres, use remote reindex (reindex.remote.whitelist debe estar definido en el destino). Los trabajos grandes se paralelizan con slices.