Mysql INSERT INTO ... VALUES and SELECT

Mixing static data with SELECT data

In certain scenarios, you might want to combine pre-defined static data with dynamic data fetched from a SELECT operation while inserting into a table. Here's how it's done:

INSERT INTO target_table (col1, col2, col3)
SELECT col1, col2, 'static_value'
FROM source_table
WHERE certain_condition;


In this example, col3 gets the same static value 'static_value' with each row insertion. It's a combo of static and dynamic values inserted in one fell swoop.

Tackling duplicates during insert

We all know that duplicates can be sneaky and disrupt data integrity. Use **INSERT IGNORE to bypass any entries causing unique constraint violations or the ON DUPLICATE KEY UPDATE clause to update existing records confidently.

INSERT INTO target_table (id, data)
SELECT source_id, source_data
FROM source_table
ON DUPLICATE KEY UPDATE data = VALUES(data);


No more nightmares about violating unique constraints! If duplication prophecy comes true, this command ensures data in target_table gets automatically updated. Database integrity stays intact!

Executing large data transfers like a Pro

Handling large data can be weightlifting for servers. For efficient large inserts:

• Group multiple operations into atomic transactions.
• Temporarily turn off indexes on the target_table during the insert.
• Call LOAD DATA INFILE into action for high-volume data insertion, if data is file-based.

What not to do: Common insert pitfalls

Here are things no one tells you about but can save your day:

• Ensure compatibility between your source and target column data types.
• Remember, database locks are real during transactions that can affect concurrent operations.
• Keep an eye on subqueries, they can slow down inserts if unindexed or monsters of complexity.