The INSERT Statement

The INSERT statement is the mechanism for loading data into your database. Data can be inserted into only one table at a time, although the data being loaded into the table can be pulled from one or more additional tables. When inserting data into a table, we do not need to provide values for every column in the table; however, we need to be aware of the columns that require non-NULL* values and the ones that do not.

Syntax:

INSERT INTO table_name (Column1,Column2,…)

Values (value1,value2,...)

Eg. INSERT INTO employee (emp_id, lname, dept_id)

VALUES (101, 'Smith', 2);

The VALUES clause must contain the same number of elements as the column list, and the data types must match the column definitions.

Sometimes, the data to be inserted needs to be retrieved from one or more tables. In this case we can feed the result set from a SELECT statement directly into an INSERT statement, as in:

INSERT INTO employee (emp_id, fname, lname, dept_id, hire_date) SELECT 101, 'Dave', 'Smith', d.dept_id, SYSDATE

FROM department d

WHERE d.name = 'Accounting';