The SQL INSERT INTO statement is used to add new records to a database.
INSERT INTO table_name (column_name [, column_name...]) VALUES (value [, value…]);
Parameters:
- INSERT INTO table_name – The name of the table where a new row will be inserted.
- column_name – Indicates the column names where the data will be added.
- VALUES value – Specifies the values to be inserted.
There is no need to specify the column names in the SQL query if values for all the columns of the table are inserted. Just make sure that the order of the values is in the same order as the columns in the table. The syntax would be as follows:
INSERT INTO table_name VALUES (value [, value…]);
If there is an auto-increment field, there is no need to insert any value, it will be generated automatically when a new record is inserted into the table.
It is also possible to insert data in specific columns.
Example:
Consider the Products table containing the following records:
id | product | quantity | price |
1 | Glass Table | 10 | 899.99 |
2 | Oak Desk | 28 | 530.50 |
3 | Gloss Coffee Table | 34 | 240.10 |
4 | Oak Table | 10 | 450.90 |
5 | Birch Chair | 8 | 60.80 |
The following SQL statement will insert a new record, but only insert data in the ‘product’ and ‘quantity’ columns (‘id’ will be updated automatically):
INSERT INTO Products (product, quantity) VALUES (‘Office Chair’, 15);
id | product | quantity | price |
1 | Glass Table | 10 | 899.99 |
2 | Oak Desk | 28 | 530.50 |
3 | Gloss Coffee Table | 34 | 240.10 |
4 | Oak Table | 10 | 450.90 |
5 | Birch Chair | 8 | 60.80 |
6 | Office Chair | 15 | null |
Notes:
- The user must have data manipulation privileges on the table or column (INSERT privilege).
- The inserted value must not conflict with all the applicable constraints (primary keys, unique indexes, CHECK constraints, and NOT NULL constraints).
Leave A Comment