In addition to the SELECT statement, which was introduced earlier in chapter “Queries“, there are three other DML statements: INSERT, UPDATE, and DELETE. Like the SELECT statement, these three modification statements operate either on tables or on views. This chapter discusses these statements in relation to tables and gives examples of their use. Additionally, it explains two other statements: TRUNCATE TABLE and MERGE, as well as the OUTPUT clause.
Whereas the TRUNCATE TABLE statement is a Transact-SQL extension to the SQL standard, MERGE is a standardized feature in SQL Server. The OUTPUT clause allows you to display explicitly the inserted (or updated) rows.
INSERT Statement
The INSERT statement inserts rows (or parts of them) into a table. It has two different forms:
INSERT [INTO] tab_name [(col_list)]
DEFAULT VALUES | VALUES ({ DEFAULT | NULL | expression } [ ,...n] )
INSERT INTO tab_name | view_name [(col_list)]
{select_statement | execute_statement}Code language: PHP (php)Using the first form, exactly one row (or part of it) is inserted into the corresponding table. The second form of the INSERT statement inserts the result set from the SELECT statement or from the stored procedure, which is executed using the EXECUTE statement. (The stored procedure must return data, which is then inserted into the table. The SELECT statement can select values from a different table or from the same table as the target of the INSERT statement, as long as the types of the columns are compatible.)
With both forms, every inserted value must have a data type that is compatible with the data type of the corresponding column of the table. To ensure compatibility, all character-based values and temporal data must be enclosed in apostrophes, while all numeric values need no such enclosing.
Inserting a Single Row
In both forms of the INSERT statement, the explicit specification of the column list is optional. This means that omitting the list of columns is equivalent to specifying a list of all columns in the table.
The option DEFAULT VALUES inserts default values for all the columns. If a column is of the data type TIMESTAMP or has the IDENTITY property, the value, which is automatically created by the system, will be inserted. For other data types, the column is set to the appropriate non-null default value if a default exists, or NULL, if it doesn’t. If the column is not nullable and has no DEFAULT value, then the INSERT statement fails and an error will be indicated.
Examples 7.1 through 7.4 insert rows into the four tables of the sample database. This action shows the use of the INSERT statement to load a small amount of data into a database.
Load data into the employee table:
USE sample;
INSERT INTO employee VALUES (25348, 'Matthew', 'Smith','d3');
INSERT INTO employee VALUES (10102, 'Ann', 'Jones','d3');
INSERT INTO employee VALUES (18316, 'John', 'Barrimore', 'd1');
INSERT INTO employee VALUES (29346, 'James', 'James', 'd2');
INSERT INTO employee VALUES (9031, 'Elsa', 'Bertoni', 'd2');
INSERT INTO employee VALUES (2581, 'Elke', 'Hansel', 'd2');
INSERT INTO employee VALUES (28559, 'Sybill', 'Moser', 'd1');Code language: PHP (php)Load data into the department table:
USE sample;
INSERT INTO department VALUES ('d1', 'Research', 'Dallas');
INSERT INTO department VALUES ('d2', 'Accounting', 'Seattle');
INSERT INTO department VALUES ('d3', 'Marketing', 'Dallas');Code language: PHP (php)Load data into the project table:
USE sample;
INSERT INTO project VALUES ('p1', 'Apollo', 120000.00);
INSERT INTO project VALUES ('p2', 'Gemini', 95000.00);
INSERT INTO project VALUES ('p3', 'Mercury', 186500.00);Code language: PHP (php)Load data into the works_on table:
USE sample;
INSERT INTO works_on VALUES (10102,'p1', 'Analyst', '2006.10.1');
INSERT INTO works_on VALUES (10102, 'p3', 'Manager', '2008.1.1');
INSERT INTO works_on VALUES (25348, 'p2', 'Clerk', '2007.2.15');
INSERT INTO works_on VALUES (18316, 'p2', NULL, '2007.6.1');
INSERT INTO works_on VALUES (29346, 'p2', NULL, '2006.12.15');
INSERT INTO works_on VALUES (2581, 'p3', 'Analyst', '2007.10.15');
INSERT INTO works_on VALUES (9031, 'p1', 'Manager', '2007.4.15');
INSERT INTO works_on VALUES (28559, 'p1', 'NULL', '2007.8.1');
INSERT INTO works_on VALUES (28559, 'p2', 'Clerk', '2008.2.1');
INSERT INTO works_on VALUES (9031, 'p3', 'Clerk', '2006.11.15');
INSERT INTO works_on VALUES (29346, 'p1','Clerk', '2007.1.4');Code language: PHP (php)There are a few different ways to insert values into a new row. Examples 7.5 through 7.7 show these possibilities.
USE sample;
INSERT INTO employee VALUES (15201, 'Dave', 'Davis', NULL);Code language: PHP (php)The INSERT statement in Example 7.5 corresponds to the INSERT statements in Examples 7.1 through 7.4. The explicit use of the keyword NULL inserts the null value into the corresponding column.
The insertion of values into some (but not all) of a table’s columns usually requires the explicit specification of the corresponding columns. The omitted columns must either be nullable or have a DEFAULT value.
USE sample;
INSERT INTO employee (emp_no, emp_fname, emp_lname)
VALUES (15201, 'Dave', 'Davis');Code language: PHP (php)Examples 7.5 and 7.6 are equivalent. The dept_no column is the only nullable column in the employee table because all other columns in the employee table were declared with the NOT NULL clause in the CREATE TABLE statement.
The order of column names in the VALUE clause of the INSERT statement can be different from the original order of those columns, which is determined in the CREATE TABLE statement. In this case, it is absolutely necessary to list the columns in the new order.
USE sample;
INSERT INTO employee (emp_lname, emp_fname, dept_no, emp_no)
VALUES ('Davis', 'Dave', 'd1', 15201);Code language: PHP (php)Inserting Multiple Rows
The second form of the INSERT statement inserts one or more rows selected with a subquery. Example 7.8 shows how a set of rows can be inserted using the second form of the INSERT statement.
Get all the numbers and names for departments located in Dallas, and load the selected data into a new table:
USE sample;
CREATE TABLE dallas_dept
(dept_no CHAR(4) NOT NULL,
dept_name CHAR(20) NOT NULL);
INSERT INTO dallas_dept (dept_no, dept_name)
SELECT dept_no, dept_name
FROM department
WHERE location = 'Dallas';Code language: PHP (php)The new table created in Example 7.8, dallas_dept, has the same columns as the department table except for the location column. The subquery in the INSERT statement selects all rows with the value ‘Dallas’ in the location column. The selected rows will be subsequently inserted in the new table. The content of the dallas_dept table can be selected with the following SELECT statement:
The result is
SELECT * FROM dallas_dept;Example 7.9 is another example that shows how multiple rows can be inserted using the second form of the INSERT statement.
Get all employee numbers, project numbers, and project enter dates for all clerks who work in project p2, and load the selected data into a new table:
USE sample;
CREATE TABLE clerk_t
(emp_no INT NOT NULL,
project_no CHAR(4),
enter_date DATE);
INSERT INTO clerk_t (emp_no, project_no, enter_date)
SELECT emp_no, project_no, enter_date
FROM works_on
WHERE job = 'Clerk'
AND project_no = 'p2';Code language: PHP (php)The new table, clerk_t, contains the following rows:
The tables dallas_dept and clerk_t (Examples 7.8 and 7.9) were empty before the INSERT statement inserted the rows. If, however, the table already exists and there are rows in it, the new rows will be appended.
Note – You can replace both statements (CREATE TABLE and INSERT) in Example 7.9 with the SELECT statement with the INTO clause (see Example 6.48 in section “Set Operators (UNION, INTERSECT and EXCEPT)“).
Table Value Constructors and INSERT
A table (or row) value constructor allows you to assign several tuples (rows) with a DML statement such as INSERT or UPDATE. Example 7.10 shows how you can assign several rows using such a constructor with an INSERT statement.
USE sample;
INSERT INTO department VALUES
('d4', 'Human Resources', 'Chicago'),
('d5', 'Distribution', 'New Orleans'),
('d6', 'Sales', 'Chicago');Code language: PHP (php)The INSERT statement in Example 7.10 inserts three rows at the same time in the department table using the table value constructor. As you can see from the example, the syntax of the constructor is rather simple. To use a table value constructor, list the values of each row inside the pair of parentheses and separate each list from the others by using a comma.
