INSERT Statement

Describes the SQL INSERT statement and common options

The INSERT statement is used to add one or more rows to a table. Columns may be specified by position or by name. If specified by position, the order of the values must match the position of the corresponding columns in the table. If columns are specified by name, they may be listed in any order.

In general, it is recommended practice to specify columns by name, since the table may be modified and column positions may change, breaking your code. Name references are position independent, and a SELECT statement with column names is more explicit and easier to read.

Columns not specified in the column list are set to their default values or to null. If the column value cannot be set to null (if it is defined as NOT NULL in the database) and it has no default value, an error will result and the INSERT will fail.

Note: In the syntax and examples below, SQL keywords appear in all caps. This is an SQL convention and it is not necessary in Studio. However, it is a useful convention to differentiate SQL statements from regular code.

There are two alternative ways to supply data for the INSERT operation. One is to specify a list of values directly:

INSERT INTO <table_name> [(<col_name1>, <col_name2>, ...)]
VALUES (<value1>, <value2>, ...)

The other alternative is to specify a SELECT query. In this case, the rows obtained from this query will be inserted into the table, so long as the column values from the query match the column values required by the INSERT:

INSERT INTO <table_name> [(<col_name1>, <col_name2>,...)]
<select statement>

The following example specifies a set of values and will insert one row:

INSERT INTO employees(fname, lname, salary)
VALUES ("John", "Smith", 25000)

The value set can also include expressions:

firstname = "John"
salary = 20000
	
INSERT INTO employees(fname, lname, salary)
VALUES(firstname, "Smith", salary + 5000)

The following example uses INSERT with a SELECT statement:

INSERT INTO students
SELECT *
FROM employees
WHERE salary > 30000