SQL INSERT INTO Statement

The SQL INSERT INTO statement is used to add new records (rows) in the table. While adding a record in the table, it is not mandatory to provide the value of all the columns, we can add the value of only few columns using the INSERT INTO statement and the remaining columns value will be set to null for that record (row).

INSERT INTO Statement Syntax

If we are adding values for some of the columns of a table then, we have to specify the column names in the INSERT INTO statement:

INSERT INTO table_name (column_name1, column_name2, .....)
VALUES (value_1, value_2,.....);

If we are adding values for the all the columns of a table then we do not need to specify the column names in the INSERT INTO statement:

INSERT INTO table_name
VALUES (value_1, value_2, value_3, value_4,......);

SQL INSERT INTO Example

Table: CUSTOMER

CUSTOMER_ID    CUSTOMER_NAME    AGE      ADDRESS   AMOUNT
-----------    -------------    ----     -------   ------
90001          Aditya           19       Noida      10000
90002          Steve            20       Delhi       9000
90010          Carl             29       Agra         600
90678          Lucy             40       Agra       19000

The following SQL statement will insert a new record in the “CUSTOMER” table.

INSERT INTO CUSTOMER
VALUES (90101, 'Ajeet', 26, 'Delhi', 5000);

Result: After the above INSERT INTO statement execution, the table would look like this:

CUSTOMER_ID    CUSTOMER_NAME    AGE      ADDRESS   AMOUNT
-----------    -------------    ----     -------   ------
90001          Aditya           19       Noida      10000
90002          Steve            20       Delhi       9000
90010          Carl             29       Agra         600
90678          Lucy             40       Agra       19000
90101          Ajeet            26       Delhi       5000

SQL insert values into specific columns

In the above example, we have inserted a complete row into the “CUSTOMER” table. However if needed, we can add the values for only specific columns. For this, we have to specify the column names in the SQL INSERT INTO statement.

Table: STUDENT

ID     NAME             AGE      ADDRESS   BRANCH
---    -------         ----     -------   ------
101    Deepak           29       Agra      ECE
102    Kevin            28       Delhi     CSE
103    Maggie           27       Lucknow   ME
104    Cate             29       Gurgaon   CSE

The following SQL statement will add a new record in the table “STUDENT”, since we have specified the values of only few columns, the remaining columns will have null values.

INSERT INTO STUDENT (ID, NAME, BRANCH)
VALUES (105, 'Chaitanya', 'CSE');

Result: After the above INSERT INTO statement execution, the table would look like this:

ID     NAME             AGE      ADDRESS   BRANCH
---    -------         ----     -------   ------
101    Deepak           29       Agra      ECE
102    Kevin            28       Delhi     CSE
103    Maggie           27       Lucknow   ME
104    Cate             29       Gurgaon   CSE
105    Chaitanya        null     null      CSE

Since we have not provided the values for columns AGE & ADDRESS, their values were set to null during insert.