Database Tutorial

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;

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 …