SQL INSERT INTO

SQL INSERT INTO Statement

The INSERT INTO statement in SQL is used to insert new records into a table. It allows you to add data row by row or insert multiple rows simultaneously. This statement is essential for populating a database with data for storage and analysis.

Syntax for INSERT INTO

The INSERT INTO statement can be written in two formats:

1. Specify Column Names

When inserting data into specific columns, list the column names and provide corresponding values:

mysql
1
2
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

2. Without Column Names

If inserting values for all columns, you can omit the column names, but the order of values must match the table's column order:

mysql
1
2
INSERT INTO table_name  
VALUES (value1, value2, value3, ...);

Examples of INSERT INTO Statement

Insert Data into All Columns

mysql
1
2
INSERT INTO Employees (EmployeeID, FirstName, LastName, Department)
VALUES (1, 'Alice', 'Johnson', 'Finance');
  • Adds a new employee record to the Employees table.

Insert Data into Specific Columns

mysql
1
2
INSERT INTO Employees (FirstName, LastName)
VALUES ('Bob', 'Smith');
  • Adds a new record with only the FirstName and LastName fields filled. Other columns (e.g., EmployeeID, Department) will remain NULL or use their default values.

Insert Multiple Rows

You can insert multiple rows into a table in a single query by separating each set of values with a comma:

mysql
1
2
3
4
5
INSERT INTO Employees (FirstName, LastName, Department)
VALUES
('Chris', 'Evans', 'IT'),
('Diana', 'Prince', 'HR'),
('Peter', 'Parker', 'Marketing');
  • Inserts three new records into the Employees table.

Auto-Increment Columns

If a table has an auto-increment column (e.g., a primary key), you don't need to specify a value for that column. It will be generated automatically.

Example

mysql
1
2
INSERT INTO Employees (FirstName, LastName, Department)
VALUES ('John', 'Doe', 'Sales');
  • The auto-increment field (e.g., EmployeeID) will generate a unique value for the new record.

Common Use Cases

  1. Populate New Tables: Add data to freshly created tables.
  2. Update Dynamic Data: Insert data that changes regularly, such as user registrations or sales transactions.
  3. Batch Insertions: Use multi-row inserts for efficient data entry in large datasets.

Best Practices

  • Use Column Names: Specify column names to avoid issues if the table structure changes.
  • Validate Data: Ensure the values being inserted match the data types and constraints of the columns.
  • Handle Nulls: Be mindful of columns that require NOT NULL values to avoid errors.
  • Leverage Auto-Increment: Let the database handle unique key generation using auto-increment columns.

Key Points to Remember

  • The INSERT INTO statement is versatile and allows for single-row and multi-row data insertion.
  • Column names can be specified to insert data selectively; otherwise, values must match the column order.
  • Auto-increment fields generate unique values automatically, simplifying primary key management.
  • Always validate your data to match table constraints for successful insertion.
Previous LessonNext Lesson