Database Toolbox

insert

Export MATLAB cell array data into database table

Syntax

insert(conn, 'tab', colnames, exdata)

Description

insert(conn, 'table', colnames, exdata) exports records from the MATLAB cell array exdata, into new rows in an existing database table tab, via the connection conn. Specify the column names for tab as strings in the MATLAB cell array, colnames.

The status of the AutoCommit flag determines if insert automatically commits the data or if you need to commit the data following the insert. View the AutoCommit flag status for the connection using get and change it using set. Commit the data using commit or issue an SQL commit statement via an exec function. Roll back the data using rollback or issue an SQL rollback statement via an exec function.

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.

Assign the data to the cell array.

exdata = {'San Diego', 88}

Create a cell array containing the column names in Temperatures.

colnames = {'City', 'Avg_Temp'}

Perform the insert.

insert(conn, 'Temperatures', colnames, exdata)

The row of data is added to the Temperatures table.

Example 2 - Insert Multiple Records

Insert a cell array, exdata, containing 28 rows of data with three columns, into the Growth table. The data columns are Date, Avg_Length, and Avg_Wt. The database connection is conn.

Insert the data.

insert(conn, 'Growth', {'Date';'Avg_Length';'Avg_Wt'}, exdata)

The records are inserted in the table.

Example 3 - Import Records, Perform Computations, and 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 (25% off) and assign it to the variable sale_price. You must convert the cell array price to a numeric array in order to perform the calculation.

sale_price =.75*[price{:}]

To export the data, it must be in a cell array. The variable sale_price is a numeric array because it was the result of the discount calculation. You must convert sale_price to a cell array. To convert the columns of data in sale_price to a cell arrays, type

sale_price = num2cell(sale_price);

Create an array, exdata, 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.

exdata = id(:,1);
exdata(:,2) = price;
exdata(:,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, exdata)

All rows of data are inserted into the Sale table.

Example 4 - Insert Followed by commit

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

Insert the cell array exdata into the column names colnames of the Error_Rate table.

insert(conn, 'Error_Rate', colnames, exdata)

Commit the data using the commit function.

commit(conn)

Alternatively, you could commit the data using the exec function with an SQL commit statement.

cursor = exec(conn,'commit');

See Also
commit, database, exec, rollback, set, update

indexinfo

isconnection