Skip to main content

SQL INSERT Statement

Body

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.

Add new comment