I recently had to transfer a relatively small amount of data, ~2GB between MS SQL databases hosted on the same server. It was a consolidation of the data from 10 total DBs, merging the data from 5 of them into a different schema on the other 5. Think 5 DBs for current data and 5 for legacy data, where the outcome was the original 5 current data DBs now contained the current and legacy data.

There was a requirement that the migration didn't blow up the transaction logs on the target DBs. I thought it would be a simple affair but after much testing I had to write a tool to generate a list of all the tables in each source DB, generate format dumping bcp commands for all the tables, then bcp commands to dump the data for each table, then SQL statements to bulk import the data using both files. The MS docs were woefully unhelpful.

Yup that's exactly it. This is an ease-of-use option for when you need to ingest somewhat small amounts of data into another database for joining. For large amounts of data, copying the CSV via a copy command should be your best bet.

Yeah. The insert statement fails when dealing with larger datasets, and the parser incurs significant overhead. The most efficient method is to use the COPY INTO command like in Databend[1] or Snowflake[2] for bulk data loading.

[1] Databend: https://docs.databend.com/guides/load-data/load-semistructur...

[2] Snowflake: https://docs.snowflake.com/en/sql-reference/sql/copy-into-ta...

[3] Data Ingestion Benchmark: https://docs.databend.com/guides/benchmark/data-ingest