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).

Was this post helpful?