How to batch insert into select

How to use the INSERT INTO SELECT statement for batch insertion

When you need to insert a large amount of data from one table to another, use INSERT INTO SELECT Inserting statements in batches can improve efficiency. Batched inserts are implemented by breaking large insert operations into smaller batches, thereby reducing the stress on the database server.

Steps:

1. Determine the batch size: Choose an appropriate batch size that can fully utilize the resources of the database server , without taking up too much memory. A good rule of thumb is to set the batch size to a few thousand rows.
2. Create a temporary table: Create a temporary table to store a subset of the data to be inserted. Temporary tables exist only within the current session and can be used to divide data into batches.
3. Insert data using the INSERT INTO SELECT statement: Write an INSERT INTO SELECT statement to insert data from the source table into the temporary table. Use the LIMIT clause to limit the number of rows inserted at a time to create a batch.

INSERT INTO #temp_table
SELECT TOP (@batch_size) *
FROM source_table
WHERE NOT EXISTS (SELECT 1 FROM destination_table WHERE id = source_table.id);

4. Insert data from the temporary table into the target table: Use the INSERT INTO SELECT statement again to insert data from the temporary table into the target table.

INSERT INTO destination_table
SELECT * FROM #temp_table;

5. Delete temporary tables: After the insertion is completed, delete the temporary tables that are no longer needed.

DROP TABLE #temp_table;

Advantages:

• Improve insertion performance
• Reduce the pressure on the database server
• Easy to control Batch size

Note:

• Using the IDENTITY_INSERT option may cause primary key conflicts.
• Ensure that the source and target tables have compatible schemas.
• Monitor the insertion process to ensure data integrity.

The above is the detailed content of How to batch insert into select. For more information, please follow other related articles on the PHP Chinese website!