Inserting Specified Values Row by Row

To add specified values to a table row by row, use Syntax 1 for the INSERT statement. SAP Sybase IQ inserts the first value you specify into the first column you specify, the second value you specify into the second column, and so on.

If you omit the list of column names, the values are inserted into the table columns in the order in which the columns were created (the same order as SELECT * retrieves). SAP Sybase IQ inserts the row into the table wherever room is available.

Values can be NULL, any positive or negative number, or a literal.

Enclose values for CHAR, VARCHAR, DATE, TIME, and TIMESTAMP or DATETIME columns in single or double quotation marks. To indicate a value with a quotation in it, use a different set of quotes for the outer quote, such as “Smith' s”.
For DATE, TIME, and TIMESTAMP or DATETIME columns, you must use a specific format.

Note: The TIMESTAMP and DATETIME data types are identical.

When you specify values for only some of the columns in a row, NULL is inserted for columns with no value specified, if the column allows NULL.

If you specify a NULL value, the destination column must allow NULLs, or the INSERT is rejected and an error message is produced in the message log. By default, SAP Sybase IQ columns allow NULLs, but you can alter this by specifying NOT NULL on the column definition in the CREATE TABLE statement, or in other ways, such as using a primary key, for example.

The following example adds 1995-06-09 into the l_shipdate column and 123 into the l_orderkey column in the lineitem table.

INSERT INTO lineitem
    (l_shipdate, l_orderkey)
VALUES('1995-06-09', 123)

If you are inserting more than a small number of data rows, it is more efficient to insert selected rows directly from a database, or to load data from a flat file with the LOAD TABLE statement, than to insert values row by row. Consider using a select statement with a few unions instead of inserting values for a few rows, because this requires only a single trip to the server.

You can use INSERT VALUES to support multiple rows.

For example:

INSERT INTO lineitem(l_shipdate, l_orderkey)
VALUES ('1995-06-09', 123),
'2001-03-28', 300),
('2010-04-01', 413);