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
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.
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.
Create a cell array containing the column names in Temperatures
.
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
.
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
.
Assign the sixth column of data to the variable price
.
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.
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
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.
Assign the column names to a string array, colnames
.
Export the data to the Sale
table.
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.
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.
Commit the data using the commit
function.
Alternatively, you could commit the data using the exec
function with an SQL commit
statement.
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 |
© 1994-2005 The MathWorks, Inc.