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.