INSERT INTO

This command adds a new record to the table.

insert_statement
    :    INSERT [INTO] table_name insert_columns_and_source
             [ON DUPLICATE KEY SET set_clause, ... ]                 -- v6.0

insert_columns_and_source
    :    DEFAULT VALUES
    |    ( column_name_list )
    |    query_expression

set_clause
    :    column_name = { DEFAULT | NULL | expr | VALUE(column_name) | VALUES(column_name) }

There are three ways to add new records 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 or load SQL dump file.

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’ )

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 )

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

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 )

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

In this command 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’

This syntax follows to non-standard mySQL syntax, as one of the simplest solution for UPSERT task. We will use mySQL docs a lots to explain this syntax.

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, Valentina DB performs an UPDATE of the old row. For example, if column a is declared as UNIQUE and contains the value 1, the following two statements have similar effect:

INSERT INTO TABLE (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;
 
UPDATE TABLE SET c=c+1 WHERE a=1;

The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.

If column b is also unique, the INSERT is equivalent to this UPDATE statement instead:

UPDATE TABLE SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated. In general, you should try to avoid using an ON DUPLICATE KEY UPDATE clause on tables with multiple unique indexes.

You can use the VALUES(col_name) function in the UPDATE clause to refer to column values from the INSERT portion of the INSERT … ON DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in the ON DUPLICATE KEY UPDATE clause refers to the value of col_name that would be inserted, had no duplicate-key conflict occurred. This function is especially useful in multiple-row inserts. The VALUES() function is meaningful only in INSERT … UPDATE statements and returns NULL otherwise. Example:

INSERT INTO TABLE (a,b,c) VALUES (1,2,3),(4,5,6)
  ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements:

INSERT INTO TABLE (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=3;
INSERT INTO TABLE (a,b,c) VALUES (4,5,6)
  ON DUPLICATE KEY UPDATE c=9;