INSERT INTO - MySQL

Overview

INSERT INTO is a MySQL command used to add a new row or rows to a table. It’s widely used for populating tables with data during database initialization, data migration, or importing data from other sources.

Syntax

INSERT INTO [database.]table_name (column1, column2, ...)
VALUES (value1, value2, ...)

Parameters:

  • [database.]table_name: Name of the table to insert data into, optionally prefixed with the database name.
  • column1, column2, …: List of columns to insert data into.
  • value1, value2, …: List of corresponding values to insert into the specified columns.

Options/Flags

AUTO_INCREMENT: If a column is declared with the AUTO_INCREMENT attribute, a unique sequential integer value is automatically assigned to each new inserted row in that column.

Examples

Inserting a single row:

INSERT INTO users (name, email, password)
VALUES ('John Doe', 'johndoe@example.com', 'secret123');

Inserting multiple rows using a subquery:

INSERT INTO orders (product_id, quantity)
SELECT product_id, SUM(quantity)
FROM sales_data
GROUP BY product_id;

Common Issues

  • Duplicates: Ensure that the combination of column values in the INSERT statement doesn’t create duplicate rows in the table, as some databases disallow duplicates.
  • Null values: Check that the data being inserted matches the data types of the columns and that it doesn’t violate any constraints (e.g., NOT NULL constraints).
  • Foreign key constraints: When inserting data into a table that has foreign key constraints, ensure that the referenced rows exist in the parent table.

Integration

With SELECT ... FOR UPDATE:

INSERT INTO orders (product_id, quantity)
SELECT product_id, SUM(quantity)
FROM sales_data
GROUP BY product_id
FOR UPDATE;

-- Lock selected rows to prevent concurrent updates
  • UPDATE: Modifies existing rows in a table.
  • DELETE: Removes rows from a table.
  • REPLACE INTO: Inserts new rows or replaces existing rows if the combination of column values already exists.