JetSQL » Statements » INSERT INTO
- Syntax:
- INSERT INTO target [(field1[, field2[, ...]])] VALUES (value1[, value2 [, ...])
INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] SELECT [source.] field1[, field2[, ...] FROM tableexpression - table
- The name of the table that is to receive the data.
- view_name
- The name of the view that is to receive the data. The view must be updateable.
- column1
- A list of one or more columns that data is to be stored in.
- value1
- A list of one or more values that is to be inserted into the columns provided. If a value is not given for a column, a default value or NULL will be inserted into the missing columns.
- target
- The name of the target table that the data is to be inserted into.
- field1
- The names of the columns the data is to be inserted into or taken from.
- source
- The name of the table that the fields are being taken from. Used only when two tables have been joined together.
- table_expression
- The name of the table that the rows are to be taken from.
The INSERT INTO statement can be used to append a record to a table or to append multiple records from one table to another.
Examples
Code:
INSERT INTO MusicArtists (FirstName, LastName, Instrument)
VALUES ('Bobby', 'Lee', 'fiddle');
Output:
(1 row(s) affected)
Explanation:
To add a specific record to a table, the INSERT INTO statement is followed by the name of the table and the names of the various fields. Each value in the VALUES clause is inserted into the field that corresponds to the value's position in the list: the first value is inserted into the first field, the second into the second field etc.
Note: If you omit the field list, you must include a value for every field in the table, otherwise the operation will fail. The values must also be separated by commas. Text and date fields also must be enclosed in single quotation marks (' ').
An AutoNumber field (also referred to as a counter field) is a data type that automatically creates and stores a number for each item added to a table. If you append records to a table with an AutoNumber field and you do not want the AutoNumbered field to be renumbered, you must include the AutoNumber field number in the query. If you do not include the AutoNumber field, the appended items will be assigned a new AutoNumber field value.
Language(s): MS SQL Server
Code:
INSERT INTO Duos (Member1)
SELECT FirstName + ' ' + LastName FROM MusicArtists;
Output:
(4 row(s) affected)
Explanation:
When using the multiple-record syntax the INSERT INTO statement precedes a SELECT statement and adds a single or multiple records to a table. This is referred to as an append query, as it copies records from one or more tables to another. The tables that contain the records being appended are not affected by the operation.
Language(s): MS SQL Server
Code:
INSERT INTO Duos (Member1)
SELECT FirstName + ' ' + LastName FROM MusicArtists
WHERE MusicianID > 3;
Output:
(1 row(s) affected)
Explanation:
This example would append to the 'Duos' table, only those records that had a unique primary key.
Language(s): MS SQL Server
Code:
INSERT INTO Residents (Name, Occupation)
SELECT Name, Occupation FROM Immigration
WHERE Residency = 'granted';
Output:
(5 row(s) affected)
Explanation:
This example demonstrates how to append records to a table from a subquery.
Language(s): MS SQL Server
Code:
INSERT INTO Insurance (Name)
SELECT Employee.Username FROM Employee
INNER JOIN Project ON Employee.EmployeeID = Project.EmployeeID
WHERE Project.ProjectName = 'Hardwork';
Output:
(6 row(s) affected)
Explanation:
As with a straight-forward SELECT statement, the FROM clause may include more than one table linked by a JOIN operation. This is illustrated in this example, which appends to the 'Insurance' table the names of all those employees involved in the 'Hardwork' project.
Language(s): MS SQL Server
