INSERT

version 11.3 (Modified)

INSERT INTO {sql_name | sql_string}

[(column_reference, ..., column_reference)]

[VALUES({[INFILE]arithmetic_expression |NULL}, ..., {[INFILE]arithmetic_expression |NULL};) |subquery]

The INSERT command is used to add data to an existing table. The table where the data is to be added is passed either using an sql_name or sql_string. The optional column_reference type arguments passed indicate the name(s) of the column(s) where the values are to be inserted. If no column_reference is passed, the value(s) inserted will be stored in the same order as in the database (1st value passed goes into 1st column, 2nd value into 2nd column, and so on).

The VALUES keyword is used to pass the value(s) to be placed in the column(s) specified. You can either pass an arithmetic_expression or NULL. Alternatively, a subquery can be passed in the VALUES keyword in order to insert a selection of data to be passed as the values.

The number of values passed in the VALUES keyword must match the number of columns specified by the column_reference type argument(s) passed and each of them must also match the data type of the corresponding column or at least be convertible to that data type.

The INFILE keyword lets you use the contents of an external file to specify the values of a new record. This keyword must only be used with VARCHAR type expressions. When the INFILE keyword is passed, the arithmetic_expression value is evaluated as a file pathname; if the file is found, the contents of the file are inserted into the corresponding column. Only fields of the Text or BLOB type can receive values from an INFILE. The contents of the file are transferred as raw data, with no interpretation.

The file searched for must be on the computer hosting the SQL engine, even if the query comes from a remote client. Similarly, the pathname must be expressed respecting the syntax of the operating system of the SQL engine. It can be absolute or relative.

The INSERT command is supported in both single- and multi-row queries. However, a multi-row INSERT statement does not allow UNION and JOIN operations.

The 4D engine allows the insertion of multi-row values, which can simplify and optimize the code, in particular when inserting large quantities of data. The syntax of multi-row insertions is of the type:

This syntax is illustrated in examples 3 and 4.

1. Here is a simple example inserting a selection from table2 into table1:

   INSERT INTO table1 (SELECT * FROM table2) 
 

2. This example creates a table and then inserts values into it:

   CREATE TABLE ACTOR_FANS
      (ID INT32, Name VARCHAR);
   INSERT INTO ACTOR_FANS
      (ID, Name)
       VALUES (1, 'Francis');

3. A multi-row syntax can be used to avoid tedious repetition of rows:

   INSERT INTO MyTable
      (Fld1,Fld2,BoolFld,DateFld,TimeFld, InfoFld)
      VALUES
         (1,1,1,'11/01/01','11:01:01','First row'),
         (2,2,0,'12/01/02','12:02:02','Second row'),
         (3,3,1,'13/01/03','13:03:03','Third row'),
         ………
         (7,7,1,'17/01/07','17:07:07','Seventh row');

4. You can also use 4D variables or arrays with a multi-row syntax:

   INSERT INTO MyTable
      (Fld1,Fld2,BoolFld,DateFld,TimeFld, InfoFld)
      VALUES
         ( :vArrId, :vArrIdx, :vArrbool, :vArrdate, :vArrL, :vArrText);

Note: You cannot combine simple variables and arrays in the same INSERT statement.

arithmetic_expression, column_reference, DELETE, subquery.