Inserting rows by using the INSERT statement
One way to insert data into tables is to use the SQL INSERT statement. This method is useful for inserting small amounts of data or inserting data from another table or view.
About this task
Use an INSERT statement to add new rows to a table or view. Using an INSERT statement, you can do the following actions:
- Specify the column values to insert a single row. You can specify constants, host variables, expressions, DEFAULT, or NULL by using the VALUES clause.
- In an application program, specify arrays of column values to insert multiple rows into a table. Use host variable arrays in the VALUES clause of the INSERT FOR n ROWS statement to add multiple rows of column values to a table.
- Include a SELECT statement in the INSERT statement to tell DB2® that another table or view contains the data for the new row or rows.
In each case, for every row that you insert, you must provide a value for any column that does not have a default value. For a column that meets one of the following conditions, specify DEFAULT to tell DB2 to insert the default value for that column:
- The column is nullable.
- The column is defined with a default value.
- The column has data type ROWID. ROWID columns always have default values.
- The column is an identity column. Identity columns always have default values.
- The column is a row change timestamp column.
The values that you can insert into a ROWID column, an identity column, or a row change timestamp column depend on whether the column is defined with GENERATED ALWAYS or GENERATED BY DEFAULT.
You can use the VALUES clause of the INSERT statement to insert a single row of column values into a table. You can either name all of the columns for which you are providing values, or you can omit the list of column names. If you omit the column name list, you must specify values for all of the columns.
- Your INSERT statement is independent of the table format. (For example, you do not need to change the statement when a column is added to the table.)
- You can verify that you are specifying the values in order.
- Your source statements are more self-descriptive.
If you do not name the columns in a static INSERT statement, and a column is added to the table, an error can occur if the INSERT statement is rebound. An error will occur after any rebind of the INSERT statement unless you change the INSERT statement to include a value for the new column. This is true even if the new column has a default value.
When you list the column names, you must specify their corresponding values in the same order as in the list of column names.
INSERT INTO YDEPT (DEPTNO, DEPTNAME, MGRNO, ADMRDEPT, LOCATION)
VALUES ('E31', 'DOCUMENTATION', '000010', 'E01', ' ');
After inserting a new department row into your YDEPT table, you can use a SELECT statement to see what you have loaded into the table. The following SQL statement shows you all of the new department rows that you have inserted:
SELECT *
FROM YDEPT
WHERE DEPTNO LIKE 'E%'
ORDER BY DEPTNO;
The result table looks similar to the following output:
DEPTNO DEPTNAME MGRNO ADMRDEPT LOCATION
====== ==================================== ====== ======== ===========
E01 SUPPORT SERVICES 000050 A00 -----------
E11 OPERATIONS 000090 E01 -----------
E21 SOFTWARE SUPPORT 000100 E01 -----------
E31 DOCUMENTATION 000010 E01 -----------
INSERT INTO YEMP
VALUES ('000400', 'RUTHERFORD', 'B', 'HAYES', 'E31', '5678', '1998-01-01',
'MANAGER', 16, 'M', '1970-07-10', 24000, 500, 1900);
INSERT INTO YEMP
(EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, JOB)
VALUES ('000410', 'MILLARD', 'K', 'FILLMORE', 'D11', '4888', 'MANAGER');