SANTA
Stealth Master
2
MONTHS
2 2 MONTHS OF SERVICE
LEVEL 1
300 XP
SQL Insert command is used to save record into a database in a specific table.
The SQL Insert command has the following syntax:
INSERT
INTO
"table_name"
(
"column1"
,
"column2"
,
...
)
VALUES
(
"value1"
,
"value2"
,
...
)
Now considering the following table.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
USA
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
USA
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
France
Now if we want to insert a new record to the table we can write the SQL statement as:
INSERT
INTO
USER
(
Firstname,
Lastname,
Age,
MaritalStatus,
Country)
VALUES
(
“tom”,
”jerry”,
”20
”,
”single”,
”USA”)
Then the table will be like
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
USA
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
USA
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
France
Tom
Jerry
20
Single
USA
We can also insert data in the table by simply passing the values and not mentioning the column names, but this technique requires that all values must be provided for all columns with respective positions as the column name.
For example:
INSERT
INTO
Users VALUES
(
“bill”,
”josh”,
”30
”,
”married”,
”UK”)
This statements insert the data as 1st value for 1st column 2nd value for 2nd column , 3rd value for 3rd column and so on.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Usa
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
If we accidently or willingly write the insert statement as:
INSERT
INTO
Users VALUES
(
“bill”,
”josh”,
”30
”)
Then there will a column mismatch error and the data will not be added to the table, because the DBMS expects that all values must be provided when no column names are mentioned.
To insert partial data in the table one can write the SQL insert statement by writing the column names and there values.
For example, if we wish to add the first name, last name and country of a person then we can write the insert statement as:
INSERT
INTO
Users (
Firstname,
Lastname,
Country)
VALUES
(
“Tommy”,
”Verticy”,
”Spain”)
Then the data will be inserted in the table successfully by putting a Null value in place of the column to which we did not entered values. See example at the last row of the following table.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Usa
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
Tommy
Verticy
Null
Null
Spain
This kind of SQL insert statement only works when null values are allowed in the table structure, if null values are not allowed then an error will occur during the insert.
The SQL Insert command has the following syntax:
INSERT
INTO
"table_name"
(
"column1"
,
"column2"
,
...
)
VALUES
(
"value1"
,
"value2"
,
...
)
Now considering the following table.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
USA
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
USA
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
France
Now if we want to insert a new record to the table we can write the SQL statement as:
INSERT
INTO
USER
(
Firstname,
Lastname,
Age,
MaritalStatus,
Country)
VALUES
(
“tom”,
”jerry”,
”20
”,
”single”,
”USA”)
Then the table will be like
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
USA
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
USA
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
France
Tom
Jerry
20
Single
USA
We can also insert data in the table by simply passing the values and not mentioning the column names, but this technique requires that all values must be provided for all columns with respective positions as the column name.
For example:
INSERT
INTO
Users VALUES
(
“bill”,
”josh”,
”30
”,
”married”,
”UK”)
This statements insert the data as 1st value for 1st column 2nd value for 2nd column , 3rd value for 3rd column and so on.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Usa
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
If we accidently or willingly write the insert statement as:
INSERT
INTO
Users VALUES
(
“bill”,
”josh”,
”30
”)
Then there will a column mismatch error and the data will not be added to the table, because the DBMS expects that all values must be provided when no column names are mentioned.
To insert partial data in the table one can write the SQL insert statement by writing the column names and there values.
For example, if we wish to add the first name, last name and country of a person then we can write the insert statement as:
INSERT
INTO
Users (
Firstname,
Lastname,
Country)
VALUES
(
“Tommy”,
”Verticy”,
”Spain”)
Then the data will be inserted in the table successfully by putting a Null value in place of the column to which we did not entered values. See example at the last row of the following table.
Users:
Firstname
Lastname
Age
Maritalstatus
Country
John
Smith
40
Married
Usa
Mathew
Simon
30
Married
UK
Bill
Steve
20
Single
Usa
Amanda
Rogers
28
Married
Germany
Steve
Hills
30
Single
france
Tom
Jerry
20
Single
USA
Bill
Josh
30
Married
UK
Tommy
Verticy
Null
Null
Spain
This kind of SQL insert statement only works when null values are allowed in the table structure, if null values are not allowed then an error will occur during the insert.