Database Tutorial

Course Tutorial Site

Site Admin

B – INSERT Statement

with 6 comments

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.

INSERT Statement

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);

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.

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);

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 );

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');

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 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;

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;

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'.

  1. 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

  2. I’ve added qualification. Please let me know if you find it supportive.

    michaelmclaughlin

    21 Jun 10 at 10:31 pm

  3. what does the word “dual” refer to in line 8?

    Joe Rambow

    28 Sep 10 at 8:47 pm

  4. 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

  5. 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

  6. James, I believe it’s fixed now.

    michaelmclaughlin

    22 Oct 14 at 2:49 am

Leave a Reply