SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-153
MXCI Examples of INSERT
MXCI Examples of INSERT
•
Insert a row into the CUSTOMER table and supply the value 'A2' for the CREDIT
column:
INSERT INTO sales.customer
VALUES (4777, 'ZYROTECHNIKS', '11211 40TH ST.',
'BURLINGTON', 'MASS.', '01803', 'A2');
--- 1 row(s) inserted.
Notice that the column name list is not specified for this INSERT statement. This
operation works because the number of values listed in the VALUES clause is
equal to the number of columns in the CUSTOMER table, and the listed values
appear in the same order as the columns specified in the CREATE TABLE
statement for the CUSTOMER table.
By issuing this SELECT statement, this specific order is displayed:
SELECT * FROM sales.customer
WHERE custnum = 4777;
CUSTNUM CUSTNAME STREET ... POSTCODE CREDIT
------- ------------- -------------- -------- ------
4777 ZYROTECHNIKS 11211 4OTH ST. ... 01803 A2
--- 1 row(s) selected.
•
Insert a row into the CUSTOMER table:
INSERT INTO sales.customer
(custnum, custname, street, city, state, postcode)
VALUES (1120, 'EXPERT MAILERS', '5769 N. 25TH PLACE',
'PHOENIX', 'ARIZONA', '85016');
--- 1 row(s) inserted.
Unlike the previous example, this INSERT does not include a value for the
CREDIT column, which has a default value. As a result, this INSERT must include
the column name list.
This SELECT statement shows the default value 'C1' for CREDIT:
SELECT * FROM sales.customer
WHERE custnum = 1120;
CUSTNUM CUSTNAME STREET ... POSTCODE CREDIT
------- -------------- -------------- -------- ------
1120 EXPERT MAILERS 5769 N. 25TH. ... 85016 C1
--- 1 row(s) selected.
•
Insert multiple rows into the JOB table by using only one INSERT statement:
INSERT INTO persnl.job
VALUES (100,'MANAGER'),