Embedded SQL to Insert records into the Database

 


This article explains how you can 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. You must specify each of the fields of the record that a value is to be assigned to and a value for that field. When you do not specify each field, the value null is inserted 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()"   )