The SQL INSERT statement is a fundamental command used to add new records into a database table. It's an essential part of data manipulation in SQL (Structured Query Language).
The basic syntax of the INSERT statement is straightforward:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
This command inserts a new row into the specified table, with values provided for each listed column.
Let's say we have a table named 'employees' with columns for id, name, and department:
INSERT INTO employees (id, name, department)
VALUES (1, 'John Doe', 'IT');
This statement adds a new employee record to the table.
SQL allows inserting multiple rows in a single statement, improving efficiency:
INSERT INTO employees (id, name, department)
VALUES
(2, 'Jane Smith', 'HR'),
(3, 'Mike Johnson', 'Sales'),
(4, 'Emily Brown', 'Marketing');
You can insert data from one table into another using a SELECT statement:
INSERT INTO new_employees (id, name, department)
SELECT id, name, department
FROM old_employees
WHERE department = 'IT';
This command copies all IT department employees from 'old_employees' to 'new_employees'.
When inserting data that might conflict with existing records, use the ON DUPLICATE KEY UPDATE clause:
INSERT INTO products (product_id, name, price)
VALUES (1, 'Laptop', 999.99)
ON DUPLICATE KEY UPDATE price = VALUES(price);
This statement updates the price if the product_id already exists, or inserts a new record if it doesn't.
Mastering the INSERT statement is crucial for effective database management. It forms the foundation for creating and updating records in SQL databases, working hand-in-hand with other SQL commands to manipulate and retrieve data efficiently.