build apps by user

Insert Formula :
Adding New Rows of Data into Database Tables

Insert formula is used when you want to add new rows of data, or records, into a database table. There are 3 types of syntax for inserting new records.

Format (Syntax) 1 – Add One Row with Fixed Values

Insert Into table ( field(s) ) Values ( value(s) )
Insert ClauseInsert Into table
It defines the destination table in the database that you want to add a new row of data.
Value Clause( field(s) ) Values ( value(s) )
The field list field(s) defines the columns in the table that are added with the items defined in the value list value(s).

Example 1

Insert into Customer (CustName, Person, Email) Values ("Company A", "Robinson", "abc@gmail.com")
It adds a new row in the Customer table with the values added in the columns CustName, Person and Email.

Format (Syntax) 2 – Add Rows Selected from Other Tables

Insert Into table ( field(s) ) Select field(s) From table(s) Where criteria
Insert ClauseInsert Into table
It defines the destination table in the database that you want to add a new row of data.
Field List( field(s) )
The field list field(s) defines the columns to be updated in the table.
Select ClauseSelect field(s) From table(s) Where criteria
It is a Select formula that returns one or more rows of data and adds the rows of values into the table.

Example 2

Insert into Customer (CustName, Person, Email) Select CustName, ContactPerson, ContactEmail From Invoice Where InvoiceNo = "INV-123456"
It adds a new row in the Customer table by selecting one row of values – the row with InvoiceNo equals to “INV-123456”, from the Invoice table.

Example 3

Insert into Customer (CustName, Contact) Select CustName, ContactPerson + ", " + ContactEmail From Invoice Where InvoiceNo = "INV-123456"
This example combines ContactPerson and ContactEmail into a single column. It demonstrates the advantage of manipulating data by using Select clause in Insert formula.

Example 4

Insert into Customer (CustName, Email) Select CustName, ContactEmail From Invoice Where InvoiceDate >= datevalue("2018/1/1")
The Select clause of this example retrieves multiple rows of Invoice records and inserts the rows into Customer table.

Example 5

Insert into Project (CustName, Address, ProjectDate) Select Customer.CustName, Customer.Address, Invoice.InvoiceDate From Customer, Invoice Where Customer.CustNo = Invoice.CustNo and Customer.CustName = "Company ABC"
This example illustrates that you can combine the data from two tables – Customer and Invoice tables, and add the retrieved data into a single table – the Project table.

Format (Syntax) 3 – Add Rows into a New Table

Select field(s) Into newtable From table(s) Where criteria
Select ClauseSelect field(s)
It defines the list of fields, or columns, that you want to retrieve from the existing tables and add to the new table.
Into ClauseInto newtable
It defines the name of the new table to be created. The new table is create with the fields defined in Select clause.
From ClauseFrom table(s) Where criteria
It defines the source tables and the conditions for selecting the rows of data to be added to the new table.

Example 6

Select CustName, Email, Address Into Customer-Automobile From Customer Where Business = "Automobile"
It selects records from Customer table in Automobile business, and add the retrieved data into the new table Customer-Automobile.

Example 7

Select Customer.CustName, Invoice.InvoiceDate, Invoice.Amount Into Customer-Invoice From Customer, Invoice Where Customer.CustNo = Invoice.CustNo
This example demonstrates the retrieval of columns from two tables and adds them into the new table Customer-Invoice.