INSERT

This statement inserts column values into one table.

Syntax

INSERT INTO table-name

[ ( column-name [ , column-name ]...) ] insert-values

[ [ UNION [ ALL ] query-specification ]...

[ ORDER BY order-by-expression [ , order-by-expression ]... ]

table-name ::= user-defined name

column-name ::= user-defined name

insert-values ::= values-clause | query-specification

values-clause ::= VALUES ( expression [ , expression ]... )
| DEFAULT VALUES

order-by-expression ::= expression [ CASE (string) | COLLATE collation-name ] [ ASC | DESC ] (see SELECT syntax)

Remarks

INSERT, UPDATE, and DELETE statements behave in an atomic manner. That is, if an insert, update, or delete of more than one row fails, then all insertions, updates, or deletes of previous rows by the same statement are rolled back.

All data types for data created prior to Pervasive.SQL 2000 (legacy data) report back as nullable. This means that you can INSERT NULL into any legacy column type without pseudo-NULL conversion. The following data types are treated as pseudo-NULL by default:


Date	Decimal	Money	Numeric
NumericSA	NumericSTS	Timestamp

Normally, when you convert a legacy column to pseudo-NULL, you lose one of the binary values, forfeiting it for use as a null value, so that you can query the column for NULL. These data types, however, because of their design, have a different, unique internal value for NULL in addition to their normal data range. With these data types, no binary values are lost if they are converted to NULL so there is no harm considering them as pseudo-NULL by default.

The rest of the data types are considered “legacy nullable,” meaning that NULL may be inserted into them. When values are queried, however, the non-NULL binary equivalent is returned. This same binary equivalent must be used in WHERE clauses to retrieve specific values.

The binary equivalents are:

•

0 for Binary types

•

Empty string from string and BLOB types (legacy types LVAR and NOTE)

CURTIME(), CURDATE() and NOW() variables

You may use the variables CURTIME(), CURDATE() and NOW() in INSERT statements to insert the current local date, time and timestamp values.

CREATE TABLE table1 (col1 DATE)

INSERT INTO table1 VALUES (CURDATE())

In order to insert the current UTC time, date, or timestamp using CURRENT_TIME() or related functions, you must structure your SQL statement as shown:

CREATE TABLE table1 (col1 DATE)

INSERT INTO table1 SELECT CURRENT_DATE()

Using Default Values

The following table describes the interaction between default values and nullable columns:

Table 35

Default Values and Nullable Columns

Column Type

Default value used if no literal default value is defined for the column:

Default value if literal value is defined:

Error—"No default value assigned for column."

As defined

Pre-v2000 Nullable

The pseudo-null value for the column.

As defined

If a statement attempts to insert an explicit NULL value into a NOT NULL column that has a default value defined, the statement fails with an error. The default value is not used in place of the attempted NULL insert.

For any column with a default value defined, the default value may be invoked in an INSERT statement by omitting the column from the insert column list or by using the keyword DEFAULT in place of the insert value.

If all the columns in a table are either nullable or have default values defined, you can insert a record containing all default values by specifying DEFAULT VALUES as the values-clause. If any column is not nullable and no default is defined, or if you want to specify a column list, you cannot use the DEFAULT VALUES clause.

Setting default values for BLOB, CLOB, or BINARY type column definitions is not currently supported.

Long Data

The maximum literal string supported by Pervasive PSQL is 15,000 bytes. You can insert data longer than this through several different methods. If you are using ODBC calls, you can use SQLPutData or bind the data using a '?' placeholder and use SQLBindParameter. Using only direct SQL statements, you can break the insert or update statement into multiple calls. Start with a statement such as this:

UPDATE table1 SET longfield = '15000 characters' WHERE restriction

Then issue the following statement to add more data:

UPDATE table1 SET longfield = notefield + '15000 more characters' WHERE restriction

Examples

The following examples shows how to add data to a table by using expressions in the VALUES clause:

CREATE TABLE t1 (c1 INT, c2 CHAR(20))

INSERT INTO t1 VALUES ((78 + 12)/3, 'This is' + CHAR(32) + 'a string')

SELECT * FROM t1

c1 c2

---------- ----------------

30 This is a string

============

The following statement adds data to the Course table by directly specifying the values in three VALUES clauses:

INSERT INTO Course(Name, Description, Credit_Hours, Dept_Name)

VALUES ('CHE 308', 'Organic Chemistry II', 4, 'Chemistry')

INSERT INTO Course(Name, Description, Credit_Hours, Dept_Name)

VALUES ('ENG 409', 'Creative Writing II', 3, 'English')

INSERT INTO Course(Name, Description, Credit_Hours, Dept_Name)

VALUES ('MAT 307', 'Probability II', 4, 'Mathematics')

============

The following INSERT statement uses a SELECT clause to retrieve from the Student table the ID numbers of students who have taken classes.

The statement then inserts the ID numbers into the Billing table.

INSERT INTO Billing (Student_ID)

SELECT ID FROM Student WHERE Cumulative_Hours > 0

============

The following example illustrates the use of the CURTIME(), CURDATE() and NOW() variables to insert the current local date, time and timestamp values inside an INSERT statement.

CREATE TABLE Timetbl (c1 time, c2 date, c3 timestamp)

INSERT INTO Timetbl(c1, c2, c3) values(CURTIME(), CURDATE(), NOW())

============

The following examples demonstrate basic usage of default values with INSERT and UPDATE statements.

CREATE TABLE t1 (c1 INT DEFAULT 10, c2 CHAR(10) DEFAULT 'abc')

INSERT INTO t1 DEFAULT VALUES

INSERT INTO t1 (c2) VALUES (DEFAULT)

INSERT INTO t1 VALUES (100, DEFAULT)

INSERT INTO t1 VALUES (DEFAULT, 'bcd')

INSERT INTO t1 VALUES (DEFAULT, DEFAULT)

SELECT * FROM t1

c1 c2

---------- ----------

UPDATE t1 SET c1 = DEFAULT WHERE c1 = 100

UPDATE t1 SET c2 = DEFAULT WHERE c2 = 'bcd'

UPDATE t1 SET c1 = DEFAULT, c2 = DEFAULT

SELECT * FROM t1

c1 c2

---------- ----------

10 abc

============

Based on the CREATE TABLE statement immediately above, the following two INSERT statements are equivalent.

INSERT INTO t1 (c1,c2) VALUES (20,DEFAULT)

INSERT INTO t1 (c1) VALUES (20)

============

The following SQL code shows the use of DEFAULT with multiple UPDATE values.

CREATE TABLE t2 (c1 INT DEFAULT 10,

c2 INT DEFAULT 20 NOT NULL,

c3 INT DEFAULT 100 NOT NULL)

INSERT INTO t2 VALUES (1, 1, 1)

INSERT INTO t2 VALUES (2, 2, 2)

SELECT * FROM t2

c1 c2 c3

---------- ---------- ----------

1 1 1

2 2 2

UPDATE t2 SET c1 = DEFAULT, c2 = DEFAULT, c3 = DEFAULT

WHERE c2 = 2

SELECT * FROM t2

c1 c2 c3

---------- ---------- ----------

1 1 1

10 20 100

============

Incorrect Examples of DEFAULT

The following examples show possible error conditions because a column is defined as NOT NULL and with no default value defined:

CREATE TABLE t1 (c1 INT DEFAULT 10, c2 INT NOT NULL, c3 INT DEFAULT 100 NOT NULL)

INSERT INTO t1 DEFAULT VALUES -- Error: No default value assigned for column <c2>.

INSERT INTO t1 VALUES (DEFAULT, DEFAULT, 10) -- Error: No default value assigned for column <c2>.

INSERT INTO t1 (c1,c2,c3) VALUES (1, DEFAULT, DEFAULT) -- Error: No default value assigned for column <c2>.

INSERT INTO t1 (c1,c3) VALUES (1, 10) -- Error: Column <c2> not nullable.

============

The following example shows what occurs when you use INSERT for IDENTITY columns and columns with default values.

CREATE TABLE t (id IDENTITY, c1 INTEGER DEFAULT 100)

INSERT INTO t (id) VALUES (0)

INSERT INTO t VALUES (0,1)

INSERT INTO t VALUES (10,10)

INSERT INTO t VALUES (0,2)

INSERT INTO t (c1) VALUES (3)

SELECT * FROM t

The SELECT shows the table contains the following rows:

The first row illustrates that if “0” is specified in the values clause for an IDENTITY column, then the value inserted is “1” if the table is empty.

The first row also illustrates that if no value is specified in the values clause for a column with a default value, then the specified default value is inserted.

The second row illustrates that if “0” is specified in the values clause for an IDENTITY column, then the value inserted is one greater than the largest value in the IDENTITY column.

The second row also illustrates that if a value is specified in the values clause for a column with a default value, then the specified value overrides the default value.

The third row illustrates that if a value other than “0” is specified in the values clause for an IDENTITY column, then that value is inserted. If a row already exists that contains the specified value for the IDENTITY column, then the message “The record has a key field containing a duplicate value(Btrieve Error 5)” is returned and the INSERT fails.

The fourth rows shows again that if “0” is specified in the values clause for an IDENTITY column, then the value inserted is one greater than the largest value in the IDENTITY column. This is true even if “gaps” exist between the values (that is, the absence of one or more rows with IDENTITY column values less than the largest value).

The fifth row illustrates that if no value is specified in the values clause for an IDENTITY column, then the value inserted is one greater than the largest value in the IDENTITY column.