Database Toolbox™

fastinsert

Add MATLAB^® data to database table

GUI Alternatives

Export data using Visual Query Builder with Data operation set to Insert. For more information on Visual Query Builder, see Using Visual Query Builder.

Syntax

fastinsert(conn, 'tablename', colnames, exdata)

Description

fastinsert(conn, 'tablename', colnames, exdata) exports records from the MATLAB variable exdata into new rows in an existing database table tablename via the connection conn. The variable exdata can be a cell array, numeric matrix, or structure. You do not specify the type of data you are exporting; the data is exported in its current MATLAB format. Specify column names for tablename 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 whether fastinsert automatically commits the data to the database. Use get to view the AutoCommit flag status for the connection and use set to change it. Use commit or issue an SQL commit statement using exec to commit the data to the database. Use rollback or issue an SQL rollback statement using exec to roll back the data.

Useupdate to replace existing data in a database.

Remarks

The fastinsert function replaces the insert function. The two functions have the same syntax, but fastinsert provides better performance and supports more object types thaninsert . If fastinsert does not work as expected, try running insert.
If an error message like the following appears when you runfastinsert, the table may be open in edit mode.
```
[Vendor][ODBC Product Driver] The database engine could 
not lock table 'TableName' because it is already in use 
by another person or process.
```
In this case, close the table in the database and rerun the fastinsert function.
The order of records in your database is not constant. Use values in column names to identify records.

Examples

Example 1 — Insert a Record

Insert a record consisting of two columns, City and Avg_Temp, into the Temperatures table. City is San Diego and Avg_Temp is 88 degrees. The database connection is conn. Assign the data to the cell array exdata.
```
exdata = {'San Diego', 88}
```
Create a cell array containing the column names in Temperatures.
```
colnames = {'City', 'Avg_Temp'}
```
Insert the data into the database.
```
fastinsert(conn, 'Temperatures', colnames, exdata)
```
The row of data is added to the Temperatures table.

Example 2 — Insert Multiple Records

Insert a cell array, exdata, that contains multiple rows of data and three columns, Date, Avg_Length, and Avg_Wt, into the Growth table. The database connection is conn.

Insert the data.

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

The records are inserted into the table.

Example 3 — Import Records, Perform Calculations, and Export Data

Import data from a database into the MATLAB workspace, perform calculations on it, and then export the results to a database.

Import all data from the products table 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 prod_name.
```
prod_name = curs.Data(:,1);
```
Assign the sixth column of data to the variable price.
```
price = curs.Data(:,6);
```
Convert the cell array price to a numeric format, and calculate off 25% of the price. Assign the result of the calculation to the variable new_price.
```
new_price =.75*[price{:}]
```
Export prod_name, price, and new_price to the Sale table. Because prod_name is a character array and price is numeric, you must export the data as a cell array. To do so, convert new_price from a numeric array back to a cell array. To convert the columns of data in new_price to a cell array, run:
```
new_price = num2cell(new_price);
```
Create an array, exdata, that contains the three columns of data to export. Put prod_name in column 1, price in column 2, and new_price in column 3.
```
exdata(:,1) = prod_name(:,1);
exdata(:,2) = price;
exdata(:,3) = new_price;
```

Assign the column names to a string array, colnames.

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

Export the data to the Sale table.
```
fastinsert(conn, 'Sale', colnames, exdata)
```
All rows of data are inserted into the Sale table.

Example 4 — Insert Numeric Data

Export tax_rate, a numeric matrix consisting of two columns, into the Tax table.

fastinsert(conn, 'Tax', {'rate','max_value'}, tax_rate)

Example 5 — Insert and Commit Data

Use the SQL commit function to commit data to a database after it has been inserted. The AutoCommit flag is off.
Insert the cell array exdata into the column names colnames of the Error_Rate table.
```
fastinsert(conn, 'Error_Rate', colnames, exdata)
```
Alternatively, commit the data using a SQL commit statement with the exec function.
```
cursor = exec(conn,'commit');
```

Example 6 — Insert BOOLEAN Data

Insert BOOLEAN data (which is represented as MATLAB type logical) into a database.

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

View the new record in the database to verify that the Discontinued field is BOOLEAN. In some databases, the MATLAB logical value 0 is shown as a BOOLEAN false, No, or a cleared check box.