Inserting Data in SQL

In order to add a record to a database table, the SQL ‘insert’ statement needs to be used. This takes the following format.

INSERT INTO table_name (column_name)
VALUES (value);

Again, as with the ‘select‘ statement, multiple columns and values can be separated by commas.

INSERT INTO table_name (column_name1, column_name2, column_name3)
VALUES (value1, value2, value3);

If data is being inserted into all columns within the table, then the column names do not need to be specified, providing the values are in the same order as the columns within the table.

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

To illustrate the insertion of data, the ‘person‘ table can again be used. Note that there is no mention of the ‘id’ field. This assumes that it is a primary key, which is set to automatically increment. A further item to mention is that all the fields here are either text or date, which is why the values are enclosed in single quotes. If any data being inserted is of a numeric data type, then the value should not be enclose in quotes.

INSERT INTO person (firstname, lastname, title, dob)
VALUES ('John', 'Smith', 'Mr', '1985-10-12');

Following the insertion of this record the contents of the ‘person‘ table will be as follows.

id firstname lastname title dob
1 Fred Bloggs Mr 1980-05-05
2 Simon Smith Mr 1960-04-01
3 Freida Bloggs Mrs 1970-10-12
4 Fiona Jones Miss 1985-05-19
5 John Smith Mr 1985-10-12