Inserting Data into an Array

Go Up to Accessing Arrays

INSERT can be used to insert data into an array column. The data to insert must exactly fill the entire array, or an error can occur.

To insert data into an array, follow these steps:

1. Declare a host-language variable to hold the array data. Use the BASED ON clause as a handy way of declaring array variables capable of holding data to insert into the entire array. For example, the following statements create three such variables:

   EXEC SQL
   BEGIN DECLARE SECTION;
   BASED ON TABLE1.CHAR_ARR char_arr;
   BASED ON TABLE1.INT_ARR int_arr;
   BASED ON TABLE1.FLOAT_ARR float_arr;
   EXEC SQL
   END DECLARE SECTION;
   

2. Load the host-language variables with data.
3. Use INSERT to write the arrays. For example,

   EXEC SQL
   INSERT INTO TABLE1 (NAME, CHAR_ARR, INT_ARR, FLOAT_ARR)
   VALUES ('Sample', :char_arr, :int_arr, :float_arr);
   

4. Commit the changes:

   EXEC SQL
   COMMIT;
   

   Important:
   When inserting data into an array column, provide data to fill all array elements, othewise the results will be unpredictable.

Advance To:

• Selecting from an Array Slice