insert (Database Toolbox)

Add MATLAB data to database table

Syntax

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

Description

insert(conn, 'table', colnames, exdata) exports records from the MATLAB variable exdata, into new rows in an existing database table tab, via the connection conn. The variable exdata can be a cell array, numeric matrix, or structure. You do not define the type of data you are exporting; the data is exported in its current MATLAB format. Specify the column names for tab as strings in the MATLAB cell array, colnames. If exdata is a structure, field names in the structure must exactly match 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. Because the data contains numeric and character data, import the data into a cell array.

conn = database('SampleDB, '', '');
curs = exec(conn, 'select * from products');
setdbprefs('DataReturnFormat','cellarray')
curs = fetch(curs);

Assign the first column of data to the variable id.

```
id = curs.Data(:,1);
```

Assign the sixth column of data to the variable price.

```
price = curs.Data(:,6);
```

Calculate the discounted price (25% off) and assign it to the variable new_price. You must convert the cell array price to a numeric matrix in order to perform the calculation.

```
new_price =.75*[price{:}]
```

Export the id, price, and new_price data to the Sale table. Because id is a character array and new_price is numeric, put the exported data in a cell array. The variable new_price is a numeric matrix because it was the result of the discount calculation. You must convert new_price to a cell array. To convert the columns of data in new_price to a cell arrays, type

```
new_price = num2cell(new_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 new_price in column three.

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

Export the new_price data into the sale_price column of the Sale table, where new_price is a numeric matrix.

insert(conn, 'Sale', {'sale_price'}, new_price)

When exporting, you do not need to define the type of data you are exporting.

Example 5--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