Modification of a Table’s Contents in SQL Server: 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};

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 data and time data must be enclosed in apostrophes, while all numeric values need no such enclosing.

1. 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.

Example 7.1

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’);

Example 7.2
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’);

Example 7.3

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);

Example 7.4

Load data into the works_on table:

USE sample;
INSERT INTO works_on VALUES (10102,’p1′, ‘Analyst’, ‘2016.10.1’);
INSERT INTO works_on VALUES (10102, ‘p3’, ‘Manager’, ‘2018.1.1’);
INSERT INTO works_on VALUES (25348, ‘p2’, ‘Clerk’, ‘2017.2.15’);
INSERT INTO works_on VALUES (18316, ‘p2’, NULL, ‘2017.6.1’);
INSERT INTO works_on VALUES (29346, ‘p2’, NULL, ‘2016.12.15’);
INSERT INTO works_on VALUES (2581, ‘p3’, ‘Analyst’, ‘2017.10.15’);
INSERT INTO works_on VALUES (9031, ‘p1’, ‘Manager’, ‘2017.4.15’);
INSERT INTO works_on VALUES (28559, ‘p1’, NULL, ‘2017.8.1’);
INSERT INTO works_on VALUES (28559, ‘p2’, ‘Clerk’, ‘2018.2.1’);
INSERT INTO works_on VALUES (9031, ‘p3’, ‘Clerk’, ‘2016.11.15’);
INSERT INTO works_on VALUES (29346, ‘p1′,’Clerk’, ‘2017.1.4’);

 There are a few different ways to insert values into a new row. Examples 7.5 through 7.7 show these possibilities.

Example 7.5

USE sample;

INSERT INTO employee VALUES (15201, ‘Dave’, ‘Davis’, NULL);

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.

Example 7.6

USE sample;

INSERT INTO employee (emp_no, emp_fname, emp_lname)

VALUES (15201, ‘Dave’, ‘Davis’);

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.

Example 7.7

USE sample;

INSERT INTO employee (emp_lname, emp_fname, dept_no, emp_no)

VALUES (‘Davis’, ‘Dave’, ‘d1’, 15201);

2. 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.

Example 7.8

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’;

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: SELECT * FROM dallas_dept;

The result is

Example 7.9 is another example that shows how multiple rows can be inserted using the second form of the INSERT statement.

Example 7.9

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’;

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.

3. 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.

Example 7.10

USE sample;

INSERT INTO department VALUES

(‘d4’, ‘Human Resources’, ‘Chicago’),

(‘d5’, ‘Distribution’, ‘New Orleans’),

(‘d6’, ‘Sales’, ‘Chicago’);

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.

Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.