Switch to: V10V9V8V7V6V5

INSERT INTO

This command adds a new record to the table.



insert_statement
    :    INSERT [INTO] table_name insert_columns_and_source
    |    INSERT INTO LINK link_name VALUES ( UINT, UINT )

insert_columns_and_source
    :    DEFAULT VALUES
    |    ( column_name_list )
    |    query_expression

There are three ways of new records adding into the table:

  • one-record INSERT command allows to add one new record into the table.
  • multi-record INSERT command supports record extraction from one table and adding them into another table.
  • file import serves for the adding of data from file.

One-record INSERT.

In the INTO statement you should specify the table into which you add a new record, the field list of this table, and the values for new record are contained in the VALUES statement. The order and the number of values should be the same as the order and the number of fields.

Field list in the INSERT command serves for the definition of the matching between data values which are contained in the VALUES statement, and the fields for which these data are intended. The value and field lists should contain the equal number of elements and the data type of each value should be compatible to the type of the corresponding field.

INSERT INTO offices ( office, city, region ) VALUES ( 22, ‘Denver’, ‘Western’ )

Adding of NULL values.

Table fields which were not defined in the list get DEFAULT value if defined or NULL value.

NULL value can be assigned explicitly, adding a field into the field list, and specifying the key word NULL for it in the value list.

INSERT INTO offices ( office, city, region ) VALUES ( 22, ‘Denver’, NULL )

Adding of all fields.

If the field list is missing, it is generated automatically and all the table fields are enumerated in it from left to right.

INSERT INTO offices  VALUES ( 22, ‘Denver’, ‘Western’, 108, 300000, 186042 )

You can add “empty” record. All fields will get default values if defined and NULL for the rest of fields.

INSERT INTO offices  DEFAULT VALUES

Parameter Binding.

The INSERT command supports binding. With a query, you can send an array or records with values. From the array, you can refer to it with the help of an index using syntax: :N.

Some advantages of linkage usage:

  1. you don’t waste CPU time for the control characters’ allocation inside the string (escape quotes);
  2. you don’t waste CPU time for building of string query using concatenation, instead you get the fixed query form and just update the values in the linked array.
  3. SQL parser gets string query in shorter form and thus spends less CPU time for the analyze;
  4. you can link BLOB values AS IS.

To use SQL binding, you need do three steps:

1) specify in the SQL command not real values, but placeholders. By SQL standard placeholder is '?' symbol. Exists also another wide used syntax ”:n”, where n is the index of parameter in the bind array. Valentina supports both these syntaxes.

2) prepare ARRAY of values according to rules of your programming language. See Valentina Reference for your API. Also check example on SQL binding from your folder of Valentina Examples.

3) Pass this array of values as additional parameter in SqlSelect(), SqlExecute() or SqlQuery() methods.

NOTE: SQL binding can be used only from some programming language. It cannot be used from regular SQL terminal such as SQL Editor of Valentina Studio.

Example:

query = "INSERT INTO tbl1 ( fldLong, fldString ) VALUES ( 2, NULL )"
 
db.SqlExecute( query )

Example:

query = "INSERT INTO tbl1 ( fldLong, fldString ) VALUES ( :1, :2 )"
 
// See Valentina EXAMPLE for your programming language how to create array!
Array binds = {"2", "NULL"}    
 
db.SqlExecute( query, bindsArray )

Multi-record INSERT.

Adds to the object table several records (more than one).

In this comand new records values are not explicit. The retrieval query inside INSERT command is the source of new records.

SQL puts some logical restrictions over such a query:

  • it is not permitted to have ORDER BY statements into the query.
  • The query results table should have the same number of field as in INSERT command, and data types of corresponding fields of query results table and object table should be compatible.


To copy old orders into the table OLDORDERS.

INSERT INTO oldorders ( order_num, order_date, amount ) 
SELECT order_num, order_date, amount
FROM orders
WHERE order_date < ‘01-JAN-90