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

No comments:

Post a Comment