SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-158
MXCI Examples of INSERT
•
Insert multiple rows into the JOB table by using only one INSERT statement:
INSERT INTO persnl.job
VALUES (100,'MANAGER'),
(200,'PRODUCTION SUPV'),
(250,'ASSEMBLER'),
(300,'SALESREP'),
(400,'SYSTEM ANALYST'),
(420,'ENGINEER'),
(450,'PROGRAMMER'),
(500,'ACCOUNTANT'),
(600,'ADMINISTRATOR'),
(900,'SECRETARY');
--- 10 row(s) inserted.
•
The PROJECT table consists of five columns using the data types NUMERIC,
VARCHAR, DATE, TIMESTAMP, and INTERVAL. Insert values by using these
types:
INSERT INTO persnl.project
VALUES (1000, 'SALT LAKE CITY', DATE '1996-10-02',
TIMESTAMP '1996-12-21:08:15:00.00', INTERVAL '30' DAY);
--- 1 row(s) inserted.
•
Suppose that CUSTLIST is a view of all columns of the CUSTOMER table except
the credit rating. Insert information from the SUPPLIER table into the CUSTOMER
table through the CUSTLIST view, and then update the credit rating:
INSERT INTO sales.custlist
(SELECT * FROM invent.supplier
WHERE suppnum = 10);
UPDATE sales.customer
SET credit = 'A4'
WHERE custnum = 10;
You could use this sequence in this situation. Suppose that one of your suppliers
has become a customer. If you use the same number for both the customer and
supplier numbers, you can select the information from the SUPPLIER table for the
new customer and insert it into the CUSTOMER table through the CUSTLIST view
(as shown in the example).
This operation works because the columns of the SUPPLIER table contain values
that correspond to the columns of the CUSTLIST view. Further, the credit rating
column in the CUSTOMER table is specified with a default value. If you want a
credit rating that is different from the default, you must update this column in the
row of new customer data.