Independent Insert
Oracle supports a single row and a multiple row INSERT statement. The single row INSERT statement works with the VALUES clause or a single row subquery, while the multiple row subquery works without the VALUES clause.
The examples in this page are the same as those that discuss the nuances of default and override signatures in this page. The table definition is:
1 2 3 4 5 6 7 | CREATE TABLE signature ( signature_id NUMBER CONSTRAINT pk1_signature PRIMARY KEY , first_name VARCHAR2(20) CONSTRAINT nn1_signature NOT NULL , middle_initial VARCHAR2(1) , last_name VARCHAR2(30) CONSTRAINT nn2_signature NOT NULL , signed DATE CONSTRAINT nn3_signature NOT NULL , signature BLOB); |
The two sections break the discussion into INSERT statements with the VALUES clause or a subquery.
INSERT Statement with VALUES Clause
You have two options with a single row subquery. You may accept the data catalog organization of columns or you may override it. The positional sequence of columns held by the database catalog after a CREATE or ALTER statement is like a formal parameter list to a function or method. The formal parameter data types govern the input you can submit based on their positional placement.
The following example uses the default signature inherited from the data catalog’s definition of the table:
1 2 3 4 5 6 7 8 9 | INSERT INTO signature VALUES ( 1 -- [SIGNATURE_ID] A number that fits within a maximum number (38 digits). ,'David' -- [FIRST_NAME] A string that fits within a 20 character width. ,'O' -- [MIDDLE_INITIAL] A string that fits within a 1 character width. ,'McKay' -- [LAST_NAME] A string that fits within a 30 character width. ,'08-JUN-1968' -- [SIGNED] A date, by leveraging the default date format. , empty_blob() -- [SIGNATURE] An empty binary large object. ); |
The following example uses the override signature defined by the list of column names in parentheses before the VALUES clause. The list of columns specify the names of the values and their ordering inside the VALUES clause.
1 2 3 4 5 6 7 8 9 10 11 | INSERT INTO signature ( signature_id , last_name -- A string that fits within a 30 character width. , first_name -- A string that fits within a 20 character width. , signed ) VALUES ( 3 -- A number that fits within a maximum number (38 digits). ,'Snow' -- A string that fits within a 30 character width. ,'Lorenzo' -- A string that fits within a 20 character width. , TO_DATE('Jun 6, 1891','MON DD, YYYY') -- An override date format. ); |
INSERT Statement with a Subquery
You have the same two options with a multiple row subquery. You may accept the data catalog organization of columns or you may override it. Like the VALUES clause, the positional sequence of columns is defined in the database catalog. It also works like a formal parameter list to a function or method. The formal parameter list data types govern the input you can submit based on their positional placement.
The following example mirrors close the default signature inherited from the data catalog’s definition of the table used above. It differs by substituting a single-row subquery to insert a single row of data. The override signature demonstrates a multiple-row subquery.
1 2 3 4 5 6 7 8 | INSERT INTO signature SELECT 1 -- A number that fits within a maximum number (38 digits). , 'David' -- A string that fits within a 20 character width. , 'O' -- A string that fits within a 1 character width. , 'McKay' -- A string that fits within a 40 character width. , '08-JUN-1968' -- A date, by leveraging the default date format. , empty_blob() -- An empty binary large object. FROM dual; |
The following example uses the override signature defined by the list of column names in parentheses before the SELECT statement, or subquery. The table doesn’t exist in the database but is fabricated. It returns a data set with the same values for everything except the ID value, which will be 4 or 5. It will insert two rows of data into the table.
1 2 3 4 5 6 7 8 9 10 11 12 | INSERT INTO signature ( signature_id , last_name -- A string that fits within a 30 character width. , first_name -- A string that fits within a 20 character width. , signed ) SELECT f.id -- A number that fits within a maximum number (38 digits). , 'Snow' -- A string that fits within a 30 character width. , 'Lorenzo' -- A string that fits within a 20 character width. , TO_DATE('Jun 6, 1891','MON DD, YYYY') -- An override date format. FROM (SELECT 4 AS ID FROM dual UNION ALL SELECT 5 AS ID FROM dual) f; |
The f.id refers to the inline view in the FROM clause. You can see the plan execution of a query here.
