DBMS SQL Insert Statement

INSERT statement is used to insert the data into the table in database. You can insert values of specified columns or an entire record containing the values for all the columns of the table. In this guide, you will learn how to use SQL Insert statement with examples.

Syntax: For inserting an entire row with all the column values:

INSERT INTO TABLE_NAME    
VALUES (value1, value2, value 3, .... Value N);

Syntax: For inserting a record with only certain column values:

INSERT INTO TABLE_NAME    
(col1, col2, col3,.... col N)   
VALUES (value1, value2, value 3, .... Value N);    

The above syntax can also be used for inserting a complete record with all values, however mentioning column names in that case would be unnecessary.

INSERT Statement Example

Let’s take a look at the table STUDENT in database:

STU_ID	STU_NAME	AGE	ADDRESS
1001	Oliver	22	Las Vegas
1002	Ajeet	18	San Diego
1003	Jadie	19	Nashville
1004	Lucy	20	Chicago
1005	Tina	22	New York

Inserting an entire row into the table

INSERT INTO STUDENT VALUES (1006, 'Jane', 21, 'Boston');  

Result: The table would look like this after inserting the row using INSERT statement:

STU_ID	STU_NAME	AGE	ADDRESS
1001	Oliver	22	Las Vegas
1002	Ajeet	18	San Diego
1003	Jadie	19	Nashville
1004	Lucy	20	Chicago
1005	Tina	22	New York
1006	Jane	21	Boston

Inserting a row with only selected column values

Let’s insert another row into the STUDENT table. This time, we will enter a record with only STU_ID, STU_NAME and AGE field. Here we are only inserting three values into the table, however the record in this table has four attributes, in this case we need to specify the column names for which we are inserting the values.

INSERT INTO STUDENT (STU_ID, STU_NAME, AGE)
 VALUES (1007, 'Dwayne', 20); 

Result: Since we didn’t specify the value for ADDRESS field for the newly inserted record, it shows address field as null for that record.

STU_ID	STU_NAME	AGE	ADDRESS
1001	Oliver	22	Las Vegas
1002	Ajeet	18	San Diego
1003	Jadie	19	Nashville
1004	Lucy	20	Chicago
1005	Tina	22	New York
1006	Jane	21	Boston
1007	Dwayne	20	null