INSERT Statement

statement_INSERT ::= INSERT INTO [scheme.]table_name [( column_name {, column_name }… )] content
content ::= VALUES ( value {, value }… ) | DEFAULT VALUES | query_expression

Description

The INSERT statement inserts one or more records into a specified table. If the column names are specified in parenthesis after the table name, the new values will be inserted into these columns in order. If the column names are not specified, the values are inserted into the table columns in order beginning with the first.

DEFAULT VALUES will insert one record and fill it with default values defined by the table.
If you wish to insert values from another table execute a query_expression to get the desired record set. The columns in the query_expression result must have the same types as the columns specified after the table name in the INSERT statement.

The number of inserted records can be obtained using the system variable @@ROWCOUNT.

If the table is prefixed with an application name (on the same server), the table is searched for in this application (schema).

You can also execute this statement with a fixed query (VIEW) instead of a table. This query must allow record insertion (this will be checked during compilation).

The INSERT statement triggers the INSERT trigger for each inserted statement. The BEFORE INSERT trigger is executed BEFORE the actual insert and the AFTER INSERT trigger is executed after the INSERT. If an error occurs during the trigger, the INSERT is rolled back.

If the INSERT statement is executed on a table that is a table with active referential integrity and the new insert values break the integrity rule, the error 40006 (REFERENTIAL_CONSTRAINT) will occur.

The user inserting records must have the privileges to do so.

Example:

INSERT INTO Employed (number, name, surname)
VALUES (55, 'John', 'Wolf')

INSERT INTO Employed DEFAULT VALUES

INSERT INTO Employed
SELECT * FROM File WHERE department_number = 1

INSERT INTO Employed (name, surname)
SELECT name_n, surname_n FROM Enrollment WHERE accepted

Table data backup into another application:

INSERT INTO Backup.Employed 
SELECT * FROM Employed

See

UPDATE Statement

SQL Statements

DELETE Statement