insert (Database Toolbox User's Guide)

Export MATLAB cell array data into database table

Syntax

insert(conn, 'tablename', colnames, data)

Description

insert(conn, 'tablename', colnames, data)

exports records from the MATLAB cell array, data, into new rows in an existing database, tablename, via the connection conn. Specify the column names for tablename in the MATLAB cell array, colnames.

The status of the autocommit flag determines if insert automatically commits the data or if an SQL commit command is needed following the insert. View the autocommit flag status for the connection using get and change it using set. Perform an SQL commit or rollback using exec.

To replace existing data instead of adding new rows, use update.

Examples

Example 1 - Insert a record

Insert one record consisting of two columns, City and Avg_Temp, into the Temperatures table. The data is San Diego, 88 degrees. The database connection is conn.

Create a cell array that will contain the record to be inserted. It is one row by two columns:

T = cell(1,2)

Assign the data to the cell array:

T = {'San Diego', 88}

Create a cell array containing the column names in Temperatures:

colnamesT = {'City', 'Avg_Temp'}

Perform the insert:

insert(conn, 'Temperatures', colnamesT, T)

The row of data is added to the Temperatures table.

Example 2 - Insert multiple records

Insert a cell array, G, containing 28 rows of data with three columns, into the Growth table. The data columns are Birthdate, Avg_Length, and Avg_Weight. The database connection is conn1.

Assign the column name strings to a cell array named colnamesG:

colnamesG = {'Birthdate', 'Avg_Length', 'Avg_Weight'}

Insert the data:

insert(conn1, 'Growth', colnamesG, G)

The records are inserted in the table.

Example 3 - Import records, perform computations, and then export data

Perform calculations on imported data and then export the data. First import all of the data in the products table:

curs = exec(conn, 'select * from products');
curs = fetch(curs)

Assign the variable id to the first column of data:

id = curs.data(:,1)

Assign the variable price to the sixth column of data:

price = curs.data(:,6)

Calculate the discounted price and assign it to the variable sale_price:

sale_price =.75*price

In order to export the data, you must convert it to a cell array. To convert the columns of data into cell arrays, type:

id = num2cell(id);
price = num2cell(price);
sale_price = num2cell(sale_price);

Create an array, data, that contains the three columns of data to be exported. Put the id data in column one, price in column two, and sale_price in column three:

data = id(:,1);
data(:,2) = price;
data(:,3) = sale_price;

Assign the column names to a string array, colnames:

colnames={'product_id', 'price', 'sale_price'};

Export the data to the Sale table:

insert(conn, 'Sale', colnames, data)

All rows of data are inserted into the Sale table.

Example 4 - Insert followed by SQL commit

This example demonstrates the use of the SQL commit command following an insert. The autocommit flag is off.

Insert the cell array data1 into the column names C of the Error_Rate table:

insert(connect, 'Error_Rate', C, data1)

Commit the data:

cursorset = exec(connect,'commit')