INSERT

Developer Reference : Data Access Methods : SQL Engine Reference : SQL Syntax Reference : INSERT

INSERT

This statement inserts column values into one table.

Syntax

INSERT INTO table-name

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

[ ON DUPLICATE KEY UPDATE column-name = < NULL | DEFAULT | expression | subquery-expression [ , column-name = ... ] >

[ [ 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

expression ::= expression - expression | expression + expression

subquery-expression ::= ( query-specification ) [ ORDER BY order-by-expression
[ , order-by-expression ]... ] [ limit-clause ]

query-specification ::= ( query-specification )

| SELECT [ ALL | DISTINCT ] [ top-clause ] select-list

FROM table-reference [ , table-reference ]...

[ WHERE search-condition ]

[ GROUP BY expression [ , expression ]...

[ HAVING search-condition ] ]

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

Remarks

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

INSERT ON DUPLICATE KEY UPDATE

Zen v13 R2 extends INSERT with INSERT ON DUPLICATE KEY UPDATE. This insert capability automatically compares unique keys for values to be inserted or updated with those in the target table. If either a duplicate primary or an index key is found, then for those rows the values are updated. If no duplicate primary or index key is found, then new rows are inserted. In popular jargon, this behavior is called an “upsert.”

The INSERT can use either a values list or a SELECT query. As with all INSERT commands, the behavior is atomic.

For illustrations of this feature, see Examples for INSERT ON DUPLICATE KEY UPDATE.

Inserting Data Longer Than the Maximum Literal String

The maximum literal string supported by Zen is 15,000 bytes. You can handle data longer than this using direct SQL statements, breaking the insert into multiple calls. Start with a statement like this:

INSERT INTO table1 SET longfield = '15000 bytes of text' WHERE restriction

Then issue the following statement to add more data:

INSERT INTO table1 SET longfield = notefield + '15000 more bytes of text' WHERE restriction

Examples

•Examples for INSERT

•Examples for INSERT ON DUPLICATE KEY UPDATE

•Errors When Using DEFAULT

Examples for INSERT

This topic illustrates simple INSERT. For the use of duplicate unique keys to update instead of insert, see Examples for INSERT ON DUPLICATE KEY UPDATE.

The following statement uses expressions in the VALUES clause to add data to a table:

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 directly adds data to the Course table using 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 time, date, and time stamp 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 example demonstrates 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

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

10 abc

100 abc

10 bcd

10 abc

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

Examples for INSERT ON DUPLICATE KEY UPDATE

This topic illustrates INSERT ON DUPLICATE KEY UPDATE. For simple INSERT, see Examples for INSERT.

For clarity, the query results in these examples show inserted values in black and updated values in red. Each example builds on the previous one, so you can execute them in series to see the behavior.

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

INSERT INTO with VALUES clause and without a column list. Unique index segment column values are available.

CREATE TABLE t1 (

a INT NOT NULL DEFAULT 10,

b INT,

c INT NOT NULL,

d INT DEFAULT 20,

e INT NOT NULL DEFAULT 1,

f INT NOT NULL DEFAULT 2,

g INT,

h INT,

PRIMARY KEY(e, f) );

CREATE UNIQUE INDEX t1_ab ON t1 ( a, b, c, d );

INSERT INTO t1 VALUES ( 1, 2, 3, 4, 5, 6, 7, 8 )

ON DUPLICATE KEY UPDATE t1.a = 10, t1.b = 20, t1.c = 30, t1.d = 40;

SELECT * FROM t1;

a b c d e f g h

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

1 2 3 4 5 6 7 8

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

INSERT INTO with VALUES clause and a complete column list. The row is updated.

INSERT INTO t1 ( a, b, c, d, e, f, g , h ) VALUES ( 1, 2, 3, 4, 5, 6, 7, 8 )

ON DUPLICATE KEY UPDATE t1.a = 10, t1.b = 20, t1.c = 30, t1.d = 40;

SELECT * FROM t1;

a b c d e f g h

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

10 20 30 40 5 6 7 8

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

INSERT INTO with VALUES clause and a partial column list. A new row is inserted, and then the row is updated.

INSERT INTO t1 ( a, b, c, d ) VALUES ( 1, 2, 3, 4 )

ON DUPLICATE KEY UPDATE t1.a = 11, t1.b = 12, t1.c = 13, t1.d = 14;

SELECT * FROM t1;

a b c d e f g h

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

10 20 30 40 5 6 7 8

1 2 3 4 1 2 (Null) (Null)

INSERT INTO t1 ( a, b, c ) VALUES ( -1, -2, -3 )

ON DUPLICATE KEY UPDATE t1.a = 11, t1.b = 12, t1.c = 13, t1.d = 14;

SELECT * FROM t1;

a b c d e f g h

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

10 20 30 40 5 6 7 8

11 12 13 14 1 2 (Null) (Null)

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

INSERT INTO with VALUES clause and DEFAULT. A row is updated to return it to an earlier state, and then it is updated based on duplicate keys.

UPDATE t1 SET a = 1, b = 2, c = 3, d = 4, e = 11, f = 12 WHERE a = 11;

SELECT * FROM t1;

a b c d e f g h

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

10 20 30 40 5 6 7 8

1 2 3 4 11 12 (Null) (Null)

INSERT INTO t1 ( a, b, c, d, e, f ) VALUES ( 1, 2, 3, 4, DEFAULT, DEFAULT )

ON DUPLICATE KEY UPDATE g = VALUES ( a ) + VALUES ( b ) + VALUES ( c ), h = VALUES ( e ) + VALUES ( f );

SELECT * FROM t1;

a b c d e f g h

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

10 20 30 40 5 6 7 8

1 2 3 4 11 12 6 3

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

Subquery expression in UPDATE SET clause to update using values from the Person table in the Demodata sample database.

INSERT INTO t1 VALUES ( 1, 2, 3, 4, 5, 6, 7, 8 )

ON DUPLICATE KEY UPDATE t1.a = 10, t1.b = 20, t1.c = ( SELECT TOP 1 id FROM demodata.person ORDER BY id ), t1.d = ( SELECT TOP 1 id FROM demodata.person ORDER BY id DESC, last_name );

SELECT * FROM t1;

a b c d e f g h

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

10 20 100062607 998332124 5 6 7 8

1 2 3 4 11 12 6 3

Errors When Using DEFAULT

The following example shows possible error conditions because a column is defined as NOT NULL 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:

1, 100

2, 1

10, 10

11, 2

12, 3

The first row illustrates that if zero 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 zero 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 zero 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 zero 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.