What is the SQL INSERT statement?
INSERT is the SQL (Structured Query Language) command that adds new records, called rows, to a table in a relational database such as Microsoft SQL Server, MySQL, PostgreSQL or Oracle. Each new row gets a value for every column you list; columns you leave out get their default value or NULL.
Syntax and examples
The basic form names the table, then the columns, then the values in the same order:
INSERT INTO customers (name, email) VALUES ('John Smith', 'john@example.com');
- Text values go in single quotes; numbers don't need them.
- Several rows at once: most database systems accept a list, as in
VALUES ('Ann', 'ann@example.com'), ('Raj', 'raj@example.com'). - Copying rows:
INSERT INTO closed_orders SELECT * FROM orders WHERE status = 'closed';fills a table from a query.
The database checks every insert against the table's rules. A row is rejected if it repeats an existing primary key, leaves a required (NOT NULL) column empty, or refers to a related record that doesn't exist (a foreign key violation).
INSERT, SELECT, UPDATE and DELETE
| Statement | What it does | Example |
|---|---|---|
| INSERT | Adds new rows | INSERT INTO customers (name) VALUES ('Ann'); |
| SELECT | Reads rows | SELECT name, email FROM customers; |
| UPDATE | Changes existing rows | UPDATE customers SET email = 'ann@example.org' WHERE id = 7; |
| DELETE | Removes rows | DELETE FROM customers WHERE id = 7; |
Together these four cover the basic create, read, update and delete (CRUD) operations. Database permissions can be granted per statement, so an account can be allowed to SELECT from a table but not INSERT into it.
INSERT and SQL injection
When an application builds an INSERT by pasting user input straight into the SQL text, an attacker can type input that changes the statement itself. That is SQL injection. The fix is a parameterized query (prepared statement): the SQL is sent with placeholders and the values are passed separately, so the database always treats them as data, never as commands.
For the CompTIA A+ exams
Core 1 (220-1201) objective 2.3 lists database servers among the server roles, and SQL is on the Core 1 acronym list. Know INSERT as the statement that adds data, alongside SELECT, UPDATE and DELETE. This course's Core 2 (220-1202) Linux lessons use a different "insert": in the vi or vim editor, pressing i enters insert mode (shown as -- INSERT -- at the bottom of the screen), Esc returns to command mode, and :wq saves and quits.