Progress
Embedded SQL-92
Guide and Reference


INSERT Rows into a Table

An INSERT statement adds one or more rows to an existing table.

EXAMPLE

The following INSERT statement adds new customer ’Nyquist’ to the customer table:

EXEC SQL
     INSERT INTO customer
          (cust_no, last_name, street, city, state)
     VALUES
          (1006, ’Nyquist’, ’1 Perpetual Street’, ’St. Cloud’, ’MN’) ; 

INSERT a Single Row into a Table

When you use an INSERT statement in an ESQL program, you can use host variables to supply the values. The columns of the customer table listed in the INSERT statement are referred to as the column list. The host variables specified in the VALUES clause of the INSERT statement are referred to as the value list.

EXAMPLE

In the next example, the values for the host variables are determined with program logic, and inserted into the customer table using the INSERT statement:

EXEC SQL BEGIN DECLARE SECTION ;
     long cust_no_v ;
     char name_v [20] ;
     char street_v [40] ;
     char city_v [10] ;
     char state_v [2] ;
 
EXEC SQL END DECLARE SECTION ;
 
/* Connect to the default database */
EXEC SQL CONNECT TO DEFAULT ;
 
/* Assign values for input host variables */
cust_no_v = 1006 ;
strcpy (name_v, "Nyquist") ;
strcpy (street_v, "1 Perpetual Street") ;
strcpy (city_v, "St. Cloud") ;
strcpy (state_v, "MN") ;
 
EXEC SQL
     INSERT INTO customer
          (cust_no, last_name, street, city, state)
     VALUES
          (:cust_no_v, :name_v, :street_v, :city_v, :state_v) ;
 
if (sqlca.sqlcode < 0)
{
     printf ("Insert statement failed (%ld : %s). \n",
          sqlca.sqlcode, sqlca.sqlerrm);
     EXEC SQL ROLLBACK WORK ;
     EXEC SQL DISCONNECT DEFAULT ;
     exit (1);
}
 
/* Success; commit the insert operation */
EXEC SQL COMMIT WORK ;
printf ("Inserted one row \n");
 
/*
** Disconnect from the default database 
*/
EXEC SQL DISCONNECT DEFAULT ; 

INSERT Multiple Rows into a Table

To INSERT more than one row, execute an INSERT statement with a sub-query.

EXAMPLE

The following code fragment shows how to INSERT multiple rows from the customer table into the mn_customer table:

EXEC SQL
     CREATE TABLE mn_customer (
      cust_no  INTEGER,
      last_name   CHAR(20),
      street  CHAR(40),
      city   CHAR(15),
      state  CHAR(2)
     );
 
 
 
EXEC SQL
     INSERT INTO mn_customer
          (cust_no, last_name, street, city, state)
          SELECT cust_no, last_name, street, city, state
          FROM customer
          WHERE state = ‘MN’ ; 

The mn_customer table must already exist before you can INSERT rows. The SELECT query expression option allows you to INSERT multiple rows at a time. The query expression must successfully return values for all the columns in the INSERT statement for the operation to succeed.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095