SQL INSERT

SQL INSERT INTO

The INSERT INTO statement adds a new record (row) to a table. The syntax specifies which table to insert into, which columns to populate, and what values to put in each column. The values must be listed in the same order as the columns they correspond to.

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

Important rules:

  • Text (string) values must be enclosed in single quotes - e.g. 'Khan'
  • Numeric values are written without quotes - e.g. 10 or 9.99
  • The number of columns listed must match the number of values provided, in the same order
  • Any column not listed in the INSERT statement must either allow NULL or have a default value

The Table Used in These Examples

All examples use the Students table below. The aim is to add new student records.

StudentIDSurnameFirstNameYearGroupGrade
1001KhanAisha10A
1002LeeJames11B
1003PatelPriya10A

The highlighted row is the new record being inserted in the example below.

INSERT INTO Examples

Inserting a Value for Every Column

When you provide a value for every column in the table, you can list the column names in order. The values must be given in the exact same order.

INSERT INTO Students (StudentID, Surname, FirstName, YearGroup, Grade)
VALUES (1003, 'Patel', 'Priya', 10, 'A');
StudentID Surname FirstName YearGroup Grade 1001 Khan Aisha 10 A 1002 Lee James 11 B INSERT INTO ↓ 1003 Patel Priya 10 A New record added (highlighted green)

Note: Surname, FirstName, and Grade are text fields so their values use single quotes. StudentID and YearGroup are integers so no quotes are needed.

Inserting Values for Specific Columns Only

You can omit columns from the list - for example, if a column has an automatically generated value (like an auto-incrementing ID) or will be filled in later. Only the listed columns receive values; any unlisted columns must allow NULL or have a default.

INSERT INTO Students (Surname, FirstName, YearGroup)
VALUES ('Ahmed', 'Sara', 10);

Here, StudentID and Grade are not specified. StudentID might be auto-generated by the database; Grade has not yet been assigned. This is valid as long as those columns permit NULL values.

 Key Takeaways

  • INSERT INTO adds a new record to a specified table.
  • Columns are listed in parentheses after the table name; corresponding values follow in VALUES, in the same order.
  • Text values must use single quotes; numeric values do not.
  • You do not need to provide a value for every column, but omitted columns must allow NULL or have a default value.