insert (Database Toolbox)

Add MATLAB data to database table

Graphical Interface

As an alternative to the insert function, you can export data using the Visual Query Builder, with the Data operation set to Insert.

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.

Remarks

If you get an error, it may be because the table is open in design mode in Access (edit mode for other databases). Close the table in the database and repeat the insert function. For example, the error might be

[Vendor][ODBC Product Driver] The database engine could not lock 
table 'TableName' because it is already in use by another person 
or process.

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 1, price in column 2, and new_price in column 3.

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');
```

Example 6--Insert BOOLEAN Data

Insert BOOLEAN data (the logical data type in MATLAB) from MATLAB to a database.

conn = database('SampleDB', '', '');
P.ProductName{1}='Chocolate Truffles';
P.Discontinued{1}=logical(0);
insert(conn, 'Products', {'ProductName';'Discontinued'}, P)

View the new record in the database to verify that value in the Discontinued field is BOOLEAN. For Microsoft Access, the MATLAB logical 0 is shown as a BOOLEAN false.

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

indexinfo isconnection