SQL INSERT INTO Statement

The INSERT INTO statement is used to insert new records in a table.

The INSERT INTO Statement

The INSERT INTO statement is used to insert a new row in a table.

SQL INSERT INTO Syntax

It is possible to write the INSERT INTO statement in two forms.

The first form doesn't specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

SQL INSERT INTO Example

We have the following "Student" table:

Student _No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kandy	1	Male	17/01/1988	C1
ST101	N.Kumanan	Jaffna	2	Male	15/12/1989	C2
ST102	S.Ranjini	Vavuniya	1	Female	17/01/1989	C1
ST103	K.Dias	Colombo	3	Male	22/07/1987	C1
ST104	A.Kavitha	Matara	3	Female	20/03/1985	C2
ST105	L.Lavanya	Galle	2	Female	22/04/1989	C2
ST106	G.Moorthy	Jaffna	2	Male	12/01/1986	C1
ST107	F.Nathan	Matara	2	Male	29/01/1990	C3
ST108	A.H.M.Akmal	Puttalam	1	Male	12/12/1990	C3

Now we want to insert a new row in the "Persons" table.

We use the following SQL statement:

INSERT INTO student VALUES
("ST109","K.Ravi","Kandy",1,"Male","1988-02-17","C1");


The "Student" table will now look like this:

Student_No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kandy	1	Male	1988-01-17	C1
ST101	N.Kumanan	Jaffna	2	Male	1989-12-15	C2
ST102	S.Ranjini	Vavuniya	1	Female	1989-01-17	C1
ST103	K.Dias	Colombo	3	Male	1987-07-22	C1
ST104	A.Kavitha	Matara	3	Female	1985-03-20	C2
ST105	L.Lavanya	Galle	2	Female	1989-04-22	C2
ST106	G.Moorthy	Jaffna	2	Male	1986-01-12	C1
ST107	F.Nathan	Matara	2	Male	1990-01-29	C3
ST108	A.H.M.Akmal	Puttalam	1	Male	1990-12-12	C3
ST109	K.Ravi	Kandy	1	Male	1988-02-17	C1

Insert Data Only in Specified Columns

It is also possible to only add data in specific columns.

The following SQL statement will add a new row, but only add data in the " "Student_No ", "Student_Name " and the "Year_Study " columns:

INSERT INTO student (Student_No,Student_Name,Year_Study)
VALUES("ST110","K.Mala",1);


The "Student" table will now look like this:

Student_No	Student_Name	Address	Year_Study	Sex	Date_of_birth	Course_No
ST100	K.Ranjan	Kandy	1	Male	1988-01-17	C1
ST101	N.Kumanan	Jaffna	2	Male	1989-12-15	C2
ST102	S.Ranjini	Vavuniya	1	Female	1989-01-17	C1
ST103	K.Dias	Colombo	3	Male	1987-07-22	C1
ST104	A.Kavitha	Matara	3	Female	1985-03-20	C2
ST105	L.Lavanya	Galle	2	Female	1989-04-22	C2
ST106	G.Moorthy	Jaffna	2	Male	1986-01-12	C1
ST107	F.Nathan	Matara	2	Male	1990-01-29	C3
ST108	A.H.M.Akmal	Puttalam	1	Male	1990-12-12	C3
ST109	K.Ravi	Kandy	1	Male	1988-02-17	C1
ST110	K.Mala	NULL	1	NULL	NULL	NULL