INSERT - SQL Command
Appends a new record to the end of a table that contains the specified field values. The INSERT SQL command has three syntaxes:
- Use the first syntax to insert specified values into the specified fields in the table.
- Use the second syntax to insert the contents of elements from an array, memory variables, or properties of an object that match the field names in the table.
- Use the third syntax to insert rows from an SQL SELECT command into the specified fields in the table.
INSERT INTO dbf_name [(fname1 [, fname2, ...])] VALUES (eExpression1 [, eExpression2, ...])
INSERT INTO dbf_name FROM ARRAY ArrayName | FROM MEMVAR | FROM NAME ObjectName
INSERT INTO dbf_name [(fname1 [, fname2, ...])] SELECT [(fname1 [, fname2, ...])] FROM tablename WHERE condition
- INSERT INTO dbf_name
- Specifies the name of the table to which the new record is appended. dbf_name can include a path and can be a name expression.
If the table you specify is not open, Visual FoxPro opens it in a new work area, and the new record is appended to the table. The new work area is not selected; the current work area remains selected.
If the table you specify is open, INSERT appends the new record to the table. If the table is open in a work area other than the current work area, it is not selected after the record is appended; the current work area remains selected.
While the command is executing, the current work area becomes the area into which the new record is being inserted. In other words, when the INSERT command is executed, it is in the context of the table being inserted into, regardless of what the current work area was before the command was issued.
- [(fname1 [, fname2 [, ...]])]
- Specifies the names of the fields in the new record into which the values are inserted.
- VALUES (eExpression1 [, eExpression2 [, ...]])
- Specifies the field values inserted into the new record. If you omit the field names, you must specify the field values in the order defined by the table structure. If SET NULL is ON, INSERT attempts to insert null values into any fields not specified in the VALUES clause.
If eExpression is a field name, it must include the table alias.
- FROM ARRAY ArrayName
- Specifies the array whose data is inserted into the new record.
The contents of the elements of the array, starting with the first element, are inserted into the corresponding fields of the record. The contents of the first array element are inserted into the first field of the new record; the contents of the second array element are inserted into the second field, and so on.
When you include the FROM ARRAY clause, Visual FoxPro disregards any default values for fields.
- FROM MEMVAR
- Specifies that the contents of memory variables are inserted into fields with the same names as the variables. If a variable does not exist with the same name as the field, the field is left empty.
- FROM NAME ObjectName
- Specifies a valid Visual FoxPro object, whose property names match the field names in the table for which you want to insert a new record containing the object's property values. You can specify any valid Visual FoxPro object, which you would typically create using the SCATTER...NAME command.
When specifying an object, if the table has a field that does match an object property, Visual FoxPro disregards the field and leaves it blank as an APPEND BLANK command was called.
Note If the type of an object's property does not match the field type in the table, Visual FoxPro generates a data type mismatch error. Use caution when specifying objects derived from Visual FoxPro classes because many native properties have types that might differ from the fields you are working with and cannot be changed.
When specifying an object, if an autoincrementing field exists in the table, you cannot have an object property that matches the autoincrementing field unless you use
SET AUTOINCERROR OFFfor the data session. Otherwise, Visual FoxPro generates an error. If you use SCATTER...NAME to create the object while SET AUTOINCERROR is set to
ON, you can use the REMOVEPROPERTY( ) function to remove any autoincrementing properties to avoid generating an error. For more information, see REMOVEPROPERTY( ) Function.
- SELECT [(fname1 [, fname2, ...])] FROM tablename WHERE condition
- Specifies the fields to select from a table or cursor to be inserted into another table or cursor. The SELECT statement cannot contain any nonSQL clauses, for example, INTO or TO clauses such as the following:
INTO Destination, TO FILE FileName [ADDITIVE], TO PRINTER [PROMPT], TO SCREEN, PREFERENCE PreferenceName [NOCONSOLE] [PLAIN] [NOWAIT]
Visual FoxPro updates the _TALLY system variable with the number or rows inserted.
After executing the INSERT command, Visual FoxPro positions the record pointer on the new record.
When you use INSERT with a SELECT statement, you must make sure that the data you insert is compatible with the data types in the table into which you are inserting. Visual FoxPro attempts to convert the data types in the SELECT cursor into the data types in the corresponding table or cursor column into which it is being inserted. If the inserted data is not compatible, precision might be lost, date data types converted to character data types, and so on.
The following example opens the
employee table and adds one record.
USE employee INSERT INTO employee (emp_no, fname, lname, officeno) ; VALUES (3022, "John", "Smith", 2101)
The following example uses USE to open the
Customer table in the
testdata database and SCATTER to copy the contents of the current record to variables. COPY STRUCTURE copies the table structure to a new table named
cust2. INSERT inserts a new record into the
cust2 table from memory variables. SELECT retrieves the data from
cust2, and BROWSE displays the new record. To clean up, USE with no table specified closes the table in the current work area, and DELETE removes
CLOSE DATABASES CLEAR OPEN DATABASE (HOME(2) + 'Data\testdata') USE Customer SCATTER MEMVAR COPY STRUCTURE TO cust2 INSERT INTO cust2 FROM MEMVAR SELECT CUST2 BROWSE USE DELETE FILE cust2.dbf
The following example inserts data from the
OrdersArchive table from a SELECT statement performed on the
INSERT INTO OrdersArchive (order_id, order_date, ship_name) ; SELECT order_id, order_date, ship_date FROM Orders ; WHERE order_date >= (DATE()-30)