Embedded SQL to Insert records into the Database

This article explains how to add a record or multiple records to a table (something like an append query).
You can use the embedded SQL to add records to a table. In this case, your code specifies the value for each field of the record. Therefore, you must specify each of the record fields assigned a value and a value for that field. When you do not specify each field, the value inserted is null for missing columns.
See the example below.

1. Open the application.
2. Right-click and select “Developer.”
3. Select “Scripts” and then select “Create.”
4. In the window, you must write your code. This example selects some records from the table CUSTOMERS and adds them to the table GREECE_CUSTOMERS.

proc ins_customer()

       start_sql “CUSTOMER” “KOSMOS”
         SELECT
         CUSTOMER.FIRST_NAME, CUSTOMER.LAST_NAME,
         CUSTOMER.ADDRESS, CUSTOMER.PHONE
         FROM
         CUSTOMER
         WHERE
         CUSTOMER.COUNTRY = “GREECE”
       end_sql
       q = QueryByName(“CUSTOMER“)
       call TExecute(q)

       {I want all the records with the value Greece in the field country}

       rcount = TRecordCount(q)

       call TFirst(q)
       for i = 1 to rcount

         start_sql “*“ “KOSMOS“
         INSERT INTO GREECE_CUSTOMERS
         end_sql
         t = QueryByName()
         call TSetFld(t, ”FIRST_NAME”, “String“, CUSTOMER.FIRST_NAME)
         call TSetFld(t, ”LAST_NAME”, “String“, CUSTOMER.LAST_NAME)
         call TSetFld(t, ”ADDRESS”, “String“, CUSTOMER.ADDRESS)
         call TSetFld(t, ”PHONE”, “String“,CUSTOMER.PHONE)
         call TExecute(t)
         call FreeEmbSQL(t)

         call TNext(q)
       next

       call FreeEmbSQL(q)

end

5. Save changes and close the window.
6. Call the procedure.
(You can call the procedure from the menu or with a button.
The command you use is “call procedure name().”
For example “call ins_customer()“ )