Using SELECT to Insert Columns
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;