When you create a new table, it does not contain any data. To enter new rows into a table, you use the INSERT statement. This statement has two general forms:
Fullselect is a select statement used in INSERT or CREATE VIEW statements, or following a predicate. A fullselect that is enclosed in parenthesis is commonly referred to as a subquery.
Depending on the default options that you have chosen when creating your table, for every row you insert, you either supply a value for each column or accept a default value. The default values for the various data types are discussed in the SQL Reference.
The following statement uses a VALUES clause to insert one row of data into the PERS table:
INSERT INTO PERS
VALUES (12, 'Harris', 20, 'Sales', 5, 18000, 1000, '1950-1-1')
The following statement uses a VALUES clause to insert three rows into the PERS table where only the IDs, the names, and the jobs are known. If a column is defined as NOT NULL and it does not have a default value, you must specify a value for it. The NOT NULL clause on a column definition in a CREATE TABLE statement can be extended with the words WITH DEFAULT. If a column is defined as NOT NULL WITH DEFAULT or a constant default such as WITH DEFAULT 10, and you do not specify the column in the column list, the default value is inserted into that column in the inserted row. For example, in the CREATE TABLE statement, a default value was only specified for DEPT column and it was defined to be 10. Hence, the department number (DEPT) is set to 10 and all other columns to null.
INSERT INTO PERS (NAME, JOB, ID)
VALUES ('Swagerman', 'Prgmr', 500),
('Limoges', 'Prgmr', 510),
('Li', 'Prgmr', 520)
The following statement returns the result of the insertions:
SELECT *
FROM PERS
ID NAME DEPT JOB YEARS SALARY COMM BIRTH_DATE
------ --------- ------ ----- ------ --------- --------- ----------
12 Harris 20 Sales 5 18000.00 1000.00 01/01/1950
500 Swagerman 10 Prgmr - - - -
510 Limoges 10 Prgmr - - - -
520 Li 10 Prgmr - - - -
Note that, in this case, values were not specified for every column. NULL values are displayed as a -. For this to work, the list of column names has to correspond both in order and in data type to the values provided in the VALUES clause. If the list of column names is omitted (as it was in the first example), the list of data values after VALUES must be in the same order as the columns in the table into which they are inserted, and the number of values must equal the number of columns in the table.
Each value must be compatible with the data type of the column into which it is inserted. If a column is defined as nullable and a value for that column is not specified, then the value NULL is given to that column in the inserted row.
The following example inserts the null value into YEARS, COMM and BIRTH_DATE since values have not been specified for those columns in the row.
INSERT INTO PERS (ID, NAME, JOB, DEPT, SALARY)
VALUES (410, 'Perna', 'Sales', 20, 20000)
The second form of the INSERT statement is very handy for populating a table with values from rows in another table. As mentioned, rather than specifying VALUES, you specify a fullselect to identify columns from rows contained in other tables and/or views.
The following example selects data from the STAFF table for members of department 38 and inserts it into the PERS table:
INSERT INTO PERS (ID, NAME, DEPT, JOB, YEARS, SALARY)
SELECT ID, NAME, DEPT, JOB, YEARS, SALARY
FROM STAFF
WHERE DEPT = 38
After this insertion, the following SELECT statement produces a result equal to the fullselect in the INSERT statement.
SELECT ID, NAME, DEPT, JOB, YEARS, SALARY
FROM PERS
WHERE DEPT = 38
The result is:
ID NAME DEPT JOB YEARS SALARY
------ --------- ------ ----- ------ ---------
30 Marenghi 38 Mgr 5 17506.75
40 O'Brien 38 Sales 6 18006.00
60 Quigley 38 Sales - 16808.30
120 Naughton 38 Clerk - 12954.75
180 Abrahams 38 Clerk 3 12009.75