Lesson 4	Inserting using a sub query
Objective	Write SQL to insert Rows using a Sub Query

Write SQL to insert Rows using a Sub Query

In the case of object tables that have references to other object tables, the INSERT statement is based on a query to the referencing object. For example, within the SALE_HEADER table, the CUST_REF column is associated with CUSTOMER_TYPE, which is maintained within CUSTOMER_OBJ_TABLE. To insert a record into the SALE_HEADER table, you must query CUSTOMER_OBJ_TABLE for values within the CUST_REF column. An example of such an INSERT statement is:

INSERT INTO sale_header 
VALUES 
(
 500,(SELECT REF(C) FROM customer_obj_table c WHERE 
 cust_id = 1),89.45, 3.58, 15, 108.03, '01-AUG-00', 
 DETAIL_TABLE()
);

In this example, the INSERT statement has a sub query, which retrieves the reference information from the CUSTOMER_OBJ_TABLE table that is maintained within the CUST_REF column in the SALE_HEADER table.
Let us look at another example that inserts a record into the PET_CARE_LOG_OBJ_TABLE table. The PRODUCT_ID column has a reference to PRODUCT_OBJ_TABLE. One example of an INSERT statement for inserting a record into this table looks like the following:

INSERT INTO pet_care_log_obj_table VALUES(
 (SELECT REF(p) FROM product_obj_table p 
    WHERE product_id = 34),
    to_date('05-JAN-00 12:30PM','dd-mon-yy hh:miPM'), 
    'JANET', 'New puppy needs vaccination.’, 
    to_date('16-JAN-00 01:30PM','dd-mon-yy hh:miPM')
);

This example is just another way of inserting into the PET_CARE_LOG_OBJ_TABLE table. Here, we have a query to select a reference for the PRODUCT_ID column from the PRODUCT_OBJ_TABLE table. This example differs from the example in the previous lesson, because this query is for getting the reference information only.
Take a look at the MouseOvers below for the syntax and an example of inserting records into object tables by using a sub query:

The INSERT statement specifying the table
The values of the columns in the order of the definition of columns during object table creation
The sub query to select reference data
The other column data
The end of the INSERT statement

INSERT INTO <table_name>
VALUES (non PET column values,
  (SElECT PEF (table alias) from <table_name) alias
    WHERE <clause>),
  (other non-PEF column value)

Insert into Syntax

The INSERT statement specifying the table
The values of the columns in the order of the definition of columns during object table creation
The sub query to select reference data
The other column data
The end of the INSERT statement

INSERT INTO sale_header
VALUES (500, 
  (SELECT REF(C) FROM customer_obj_table
    where CUST_ID=1),
	89.45, 3.58, 15, 108.03, '01-AUG-00',
	detail_table()
);

SQL Insert Syntax
In the next lesson, we will look at writing DML statements to update the records within object tables.