4 ms·

For small datasets, this is good, but for anything significant, using the database's native bulk load will be more efficient than INSERT statements.

retox 2y ago
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.
- bormaj 2y ago
  Isn't there a query option to disable/configure log writes for cases like this?
- ryanwaldorf 2y ago
  Why were folks concerned about the transaction logs?
ryanwaldorf 2y ago
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.
BohuTANG 2y ago
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-semistructured/load-csv https://docs.databend.com/guides/load-data/load-semistructur... [2] Snowflake: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table https://docs.snowflake.com/en/sql-reference/sql/copy-into-ta... [3] Data Ingestion Benchmark: https://docs.databend.com/guides/benchmark/data-ingest https://docs.databend.com/guides/benchmark/data-ingest

retox 2y ago

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.

bormaj 2y ago
Isn't there a query option to disable/configure log writes for cases like this?
ryanwaldorf 2y ago
Why were folks concerned about the transaction logs?

ryanwaldorf 2y ago

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.

BohuTANG 2y ago

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-semistructured/load-csv https://docs.databend.com/guides/load-data/load-semistructur... [2] Snowflake: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table https://docs.snowflake.com/en/sql-reference/sql/copy-into-ta... [3] Data Ingestion Benchmark: https://docs.databend.com/guides/benchmark/data-ingest https://docs.databend.com/guides/benchmark/data-ingest