Using SELECT to Insert Columns

From InterBase
Jump to: navigation, search

Go Up to Inserting Data (Embedded SQL Guide)


To insert values from one table into another row in the same table or into a row in another table, use a SELECT statement to specify a list of insertion values. For example, the following INSERT statement copies DEPARTMENT and ­BUDGET information about the publications department from the OLDDEPT table to the DEPARTMENT table. It also illustrates how values can be hard-coded into a SELECT statement to substitute actual column data.

EXEC SQL
INSERT INTO DEPARTMENTS (DEPT_NO, DEPARTMENT, BUDGET)
SELECT DEPT_NO, 'Publications', BUDGET
FROM OLDDEPT
WHERE DEPARTMENT = 'Documentation';

The assignments in the SELECT can include arithmetic operations. For example, suppose an application keeps track of employees by using an employee number. When a new employee is hired, the following statement inserts a new employee row into the EMPLOYEE table, and assigns a new employee number to the row by using a SELECT statement to find the current maximum employee number and adding one to it. It also reads values for LAST_NAME and FIRST_NAME from the host variables, lastname, and firstname.

EXEC SQL
INSERT INTO EMPLOYEE (EMP_NO, LAST_NAME, FIRST_NAME)
SELECT (MAX(EMP_NO) + 1, :lastname, :firstname)
FROM EMPLOYEE;

Advance To: