Technical guide

How should a database transfer be planned?

A successful transfer is more than a connection string. Source query, target table, key fields, error behavior and rerun strategy should be decided before scheduling the job.

Checklist

Questions to answer before the first transfer

What is the source?

Is it a single table, a joined query, or a date-filtered result set? The source query should be deterministic.

How is the target updated?

Is insert enough, or should repeated records update existing rows? This is where the upsert key is selected.

What happens on error?

Stopping on first error, skipping bad rows or rolling back the run all create different operational outcomes.

Column mapping principles

Column mapping connects source query fields to target table fields intentionally. Even when names match, data type, length, null behavior and default values should be reviewed. Date formats, decimal separators and character sets can create reporting issues after the transfer.

A practical approach is to test with a small sample first, then run the same job over a larger date range. Row counts and representative records should be checked before the schedule is enabled.

When is upsert needed?

Upsert is needed when rerunning the same job should not create duplicate target rows. Order number, invoice number, product code or composite keys can be used to define uniqueness. Choosing the wrong key can cause data loss or unnecessary updates.

Insert

Adds new rows on every run. Useful for log and movement tables.

Update

Changes existing rows. It should be used carefully for reference data.

Upsert

Updates when a row exists and inserts when it does not. Useful for synchronized summary tables.

Truncate + load

Simple for small reference tables, risky for large operational tables.