B – INSERT Statement
The INSERT
statement lets you insert one to many new records in a table with only a single SQL statement. You use the VALUES
clause in most ANSI SQL:92 compliant databases when you want to insert a single row of data.
Several implementations, like MySQL and SQL Server, let you insert multiple rows within a VALUES
clause by including them inside a comma-delimited set of parenthetical rows. You can also insert multiple rows without the VALUES
clause by providing a query as the source of data. When you provide a query, you must remove the VALUES
clause or you trigger an error.
Oracle
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); |
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. ); |
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 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; |
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; |
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.
MySQL
MySQL also supports a single row and a multiple row INSERT
statement like Oracle. The single row INSERT
statement works with the VALUES
clause or a single row subquery. Unlike Oracle, a multiple row INSERT
statement works with the VALUES
clause by using a comma-delimited set of records or a subquery.
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 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , first_name VARCHAR(20) NOT NULL , middle_initial VARCHAR(1) , last_name VARCHAR(30) NOT NULL , signed DATE NOT NULL , signature BLOB); |
CREATE TABLE signature ( signature_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT , first_name VARCHAR(20) NOT NULL , middle_initial VARCHAR(1) , last_name VARCHAR(30) NOT NULL , signed DATE 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 | INSERT INTO signature VALUES ( NULL ,'David' ,'O' ,'McKay' ,'19680608' , NULL ); |
INSERT INTO signature VALUES ( null ,'David' ,'O' ,'McKay' ,'19680608' , null );
The following example uses the override signature defined by the list of column names in parentheses before the VALUES
clause. You should note that this override signature leaves the required PRIMARY KEY
column off the list because MySQL automatically populates it. 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 | INSERT INTO signature ( last_name , first_name , signed ) VALUES ('Snow' ,'Lorenzo' ,'18910606'); |
INSERT INTO signature ( last_name , first_name , signed ) VALUES ('Snow' ,'Lorenzo' ,'18910606');
You also have the ability to insert multiple rows with the VALUES
clause. Each row is enclosed in a list of comma separated values
1 2 3 4 5 | INSERT INTO signature ( last_name, first_name, signed ) VALUES ('Snow','Lorenzo','18910608') , ('Smith','Joseph','19011202'); |
INSERT INTO signature ( last_name, first_name, signed ) VALUES ('Snow','Lorenzo','18910608') , ('Smith','Joseph','19011202');
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 , 'David' , 'O' , 'McKay' , '19680608' , NULL FROM dual; |
INSERT INTO signature SELECT 1 , 'David' , 'O' , 'McKay' , '19680608' , null 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 , first_name , signed ) SELECT f.id , 'Snow' , 'Lorenzo' , '19810606' FROM (SELECT 4 AS ID FROM dual UNION ALL SELECT 5 AS ID FROM dual) f; |
INSERT INTO signature ( signature_id , last_name , first_name , signed ) SELECT f.id , 'Snow' , 'Lorenzo' , '19810606' 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.
You should note that the AUTO_INCREMENT
column is provided in the foregoing example because the query is overriding the automatic numbering. It’s not a good idea or practice but it is possible.
SQL Server
SQL Server under research and development …
Written by michaelmclaughlin
October 8th, 2009 at 8:27 pm
Posted in
6 Responses to 'B – INSERT Statement'
Subscribe to comments with RSS or TrackBack to 'B – INSERT Statement'.
Inside the last table, what is the meaning of “f.id” on line 6 and “f” on line 12?
shayne jensen
12 May 10 at 7:27 pm
I’ve added qualification. Please let me know if you find it supportive.
michaelmclaughlin
21 Jun 10 at 10:31 pm
what does the word “dual” refer to in line 8?
Joe Rambow
28 Sep 10 at 8:47 pm
The
dual
refers to a pseudo table that exists in Oracle, MySQL, and SQL Server. It may be excluded generally from MySQL and SQL Server but there are instances where SQL Server requires it.michaelmclaughlin
3 Oct 10 at 2:49 pm
After the default signature block of code the last sentence in that paragraph doesn’t make a lot of sense grammatically. The sentence is; This instructs the database engine that to map the values to these columns.
James Park
10 Oct 13 at 7:12 pm
James, I believe it’s fixed now.
michaelmclaughlin
22 Oct 14 at 2:49 am