SQL/MX Comparison Guide for SQL/MP Users

DML Features
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
3-14
INSERT Statement
disabled. See the Defaults Table entry in the SQL/MX Reference Manual for further
information.
INSERT Statement
RETURNING LASTSYSKEY Clause
NonStop SQL/MP supports the RETURNING LASTSYSKEY clause for the INSERT
statement. NonStop SQL/MX does not support this option.
Insert Rows in Order
NonStop SQL/MX allows you to insert rows in order with an ORDER BY clause on the
INSERT statement. When selecting rows from one table to be inserted into another, an
ORDER BY clause can reorder the sequence of the insertion so that the inserts are
done more efficiently. You cannot use ORDER BY in NonStop SQL/MX when the
SELECT statement is a subquery. The ORDER BY clause cannot be used in
embedded SQL programs. The ORDER BY clause is an SQL/MX extension to the
ANSI-92 standard. For additional information about the ORDER BY clause, see the
SQL/MX Reference Manual.
Inserting Multiple Rows
In NonStop SQL/MP, if you use the VALUES keyword, you can insert only one row. For
example, this SQL/MP statement inserts a single row of values:
INSERT INTO JOB VALUES (1000, 'MANAGER');
In NonStop SQL/MX, if you use the VALUES keyword, you can insert multiple row-
value constructors. For example, this SQL/MX statement inserts two rows into table
JOB1:
INSERT INTO JOB1 VALUES (1000, 'MANAGER'), (2000, 'SUPERVISOR')
In general, in NonStop SQL/MX, each row to be inserted is specified as a list of value
expressions or a row subquery. A value in a row can also be a scalar subquery. For
example, this SQL/MX statement inserts two rows into table JOB:
INSERT INTO JOB VALUES (1000, 'MANAGER'), ((SELECT *
FROM JOB1 WHERE JOBCODE = 2000));
In NonStop SQL/MP you usually perform multiple row inserts by setting a single row
insert to run in a loop. Even though NonStop SQL/MP is inserting a single row,
because it is looped, it will do this insert as a multiple row insert in DP2 using virtual
sequential block buffering (VSBB).
NonStop SQL/MX’s architecture handles this differently and it is more efficient to use
rowsets to perform multiple row inserts. If VSBB is on, EXPLAIN output will show
INSERT_VSBB as the operator.