datainsert
(To be removed) Export MATLAB data into database table
The datainsert
function will be removed in a future release. Use
the sqlwrite
function instead. For details, see Version History.
Description
Examples
Export MATLAB Cell Array Data
Use an ODBC connection and a cell array to export inventory data from MATLAB into a MySQL® database table.
Create a database connection conn
to the MySQL database using the native ODBC interface. Here, this code
assumes that you are connecting to an ODBC data source named
MySQL
with a user name and password. This database
contains the table inventoryTable
with these columns:
productNumber
Quantity
Price
inventoryDate
conn = database('MySQL','username','pwd');
Display the last rows in inventoryTable
before
inserting data.
curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = ... [14] [2000] [19.1000] '2014-10-22 10:52...' [15] [1200] [20.3000] '2014-10-22 10:52...' [16] [1400] [34.3000] '1999-12-31 00:00...'
Create a cell array of column names for the database table
inventoryTable
.
colnames = {'productNumber','Quantity','Price','inventoryDate'};
Define a cell array of input data to insert.
data = {50 100 15.50 datestr(now,'yyyy-mm-dd HH:MM:SS')};
Insert the input data into the table inventoryTable
using the database connection.
tablename = 'inventoryTable';
datainsert(conn,tablename,colnames,data)
Display the inserted data in inventoryTable
.
curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = ... [15] [1200] [20.3000] '2014-10-22 10:52...' [16] [1400] [34.3000] '1999-12-31 00:00...' [50] [ 100] [15.5000] '2014-10-22 11:29...'
The last row contains the inserted data.
After you finish working with the cursor
object, close it.
close(curs)
Close the database connection.
close(conn)
Export MATLAB Table Data
Use a JDBC connection and a MATLAB table to export inventory data from MATLAB into a MySQL database table.
Create a database connection conn
to the MySQL database using the JDBC driver. Use the
Vendor
name-value pair argument of the
database
function to specify a connection to a
MySQL database. Here, this code assumes that you are connecting to a
database named dbname
on a database server named
sname
with a user name and password. This database
contains the table inventoryTable
with these columns:
productNumber
Quantity
Price
inventoryDate
conn = database('dbname','username','pwd', ... 'Vendor','MySQL', ... 'Server','sname');
Display the last rows in inventoryTable
before
inserting data.
curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = ... [14] [2000] [19.1000] '2014-10-22 10:52...' [15] [1200] [20.3000] '2014-10-22 10:52...' [16] [1400] [34.3000] '1999-12-31 00:00...'
Create a cell array of column names for the database table
inventoryTable
.
colnames = {'productNumber','Quantity','Price','inventoryDate'};
Define the input data as a table.
data = table(50,100,15.50,{datestr(now,'yyyy-mm-dd HH:MM:SS')}, ... 'VariableNames',colnames);
Insert the input data into the table inventoryTable
using the database connection.
tablename = 'inventoryTable';
datainsert(conn,tablename,colnames,data)
Display the inserted data in inventoryTable
.
curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = ... [15] [1200] [20.3000] '2014-10-22 10:52...' [16] [1400] [34.3000] '1999-12-31 00:00...' [50] [ 100] [15.5000] '2014-10-22 11:29...'
The last row contains the inserted data.
After you finish working with the cursor
object, close it.
close(curs)
Close the database connection.
close(conn)
Export MATLAB Structure Data
Use an ODBC connection and a MATLAB structure to export inventory data from MATLAB into a MySQL database table.
Create a database connection conn
to the MySQL database using the native ODBC interface. Here, this code
assumes that you are connecting to an ODBC data source named
MySQL
with a user name and password. This database
contains the table inventoryTable
with these columns:
productNumber
Quantity
Price
inventoryDate
conn = database('MySQL','username','pwd');
Display the last rows in inventoryTable
before
inserting data.
curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = ... [14] [2000] [19.1000] '2014-10-22 10:52...' [15] [1200] [20.3000] '2014-10-22 10:52...' [16] [1400] [34.3000] '1999-12-31 00:00...'
Create a cell array of column names for the database table
inventoryTable
.
colnames = {'productNumber','Quantity','Price','inventoryDate'};
Define the input data as a structure.
data = struct('productNumber',50,'Quantity',100,'Price',15.50, ... 'inventoryDate',datestr(now,'yyyy-mm-dd HH:MM:SS'));
Insert the input data into the table inventoryTable
using the database connection.
tablename = 'inventoryTable';
datainsert(conn,tablename,colnames,data)
Display the inserted data in inventoryTable
.
curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = ... [15] [1200] [20.3000] '2014-10-22 10:52...' [16] [1400] [34.3000] '1999-12-31 00:00...' [50] [ 100] [15.5000] '2014-10-22 11:29...'
The last row contains the inserted data.
After you finish working with the cursor
object, close it.
close(curs)
Close the database connection.
close(conn)
Export MATLAB Numeric Matrix Data
Use a JDBC connection and a numeric matrix to export sales data from MATLAB into a MySQL database table.
Create a database connection conn
to the MySQL database using the JDBC driver. Use the
Vendor
name-value pair argument of
database
to specify a connection to a MySQL database. Here, this code assumes that you are connecting to a
database named dbname
on a database server named
sname
with a user name and password. This database
contains the table salesVolume
with the column
stockNumber
and columns for each month of the
year.
conn = database('dbname','username','pwd', ... 'Vendor','MySQL', ... 'Server','sname');
Display the last rows in salesVolume
before inserting
data.
curs = exec(conn,'SELECT * FROM salesVolume');
curs = fetch(curs);
curs.Data
ans = Columns 1 through 8 ... [470816] [3100] [9400] [1540] [1500] [1350] [1190] [ 900] [510099] [ 235] [1800] [1040] [ 900] [ 750] [ 700] [ 400] [899752] [ 123] [1700] [ 823] [ 701] [ 689] [ 621] [ 545] Columns 9 through 13 ... [867] [ 923] [1400] [ 3000] [35000] [350] [ 500] [ 100] [ 3000] [18000] [421] [ 495] [ 650] [ 4200] [11000]
Create a cell array of column names for the database table
salesVolume
.
colnames = {'stockNumber','January','February' ... 'March','April','May', ... 'June','July','August', ... 'September','October','November', ... 'December'};
Define the numeric matrix data
that contains the sales
volume data.
data = [777666,0,350,400,450,250,450,500,515, ...
235,100,300,600];
Insert the contents of data
into the table
salesVolume
using the database connection.
tablename = 'salesVolume';
datainsert(conn,tablename,colnames,data)
Display the inserted data in salesVolume
.
curs = exec(conn,'SELECT * FROM salesVolume');
curs = fetch(curs);
curs.Data
ans = Columns 1 through 8 ... [510099] [ 235] [1800] [1040] [ 900] [ 750] [ 700] [ 400] [899752] [ 123] [1700] [ 823] [ 701] [ 689] [ 621] [ 545] [777666] [ 0] [ 350] [ 400] [ 450] [ 250] [ 450] [ 500] Columns 9 through 13 ... [350] [ 500] [ 100] [ 3000] [18000] [421] [ 495] [ 650] [ 4200] [11000] [515] [ 235] [ 100] [ 300] [ 600]
The last row contains the inserted data.
After you finish working with the cursor
object, close it.
close(curs)
Close the database connection.
close(conn)
Input Arguments
conn
— Database connection
connection
object
Database connection, specified as an ODBC connection
object or JDBC connection
object created using the
database
function.
tablename
— Database table name
string scalar | character vector
Database table name, specified as a string scalar or character vector denoting the name of a table in the database.
Example: "employees"
Data Types: string
| char
colnames
— Database table column names
cell array of character vectors | string array
Database table column names, specified as a cell array of one or more character vectors or a
string array to denote the columns in the existing database table
tablename
.
Example: {'col1','col2','col3'}
Data Types: cell
| string
data
— Insert data
cell array | numeric matrix | table | structure | dataset
Insert data, specified as a cell array, numeric matrix, table, structure, or dataset array.
If you are connecting to a database using a JDBC driver, then convert the
insert data to a supported format before running
datainsert
. If data
contains
MATLAB dates, times, or timestamps, use this formatting:
Dates must be character vectors of the form
yyyy-mm-dd
.Times must be character vectors of the form
HH:MM:SS
.Timestamps must be character vectors of the form
yyyy-mm-dd HH:MM:SS.FFF
.
The database preference settings
NullNumberWrite
and
NullStringWrite
do not apply to this function. If
data
contains null
entries and
NaN
s, convert these entries to an empty value
''
.
The datainsert
function supports inserting
MATLAB date numbers and NaN
s when
data
is a numeric matrix. Date numbers inserted into
database date and time columns convert to java.sql.Date
.
Upon insertion into the target database, any converted date and time data
accurately reverts to the native database format.
If data
is a structure, then field names in the
structure must match colnames
.
If data
is a table or a dataset array, then the
variable names in the table or dataset array must match
colnames
.
Tips
When you establish a database connection using a JDBC driver,
datainsert
performs faster thanfastinsert
.datainsert
uses the SQLTRANSACTION
statement to insert records with faster performance for these databases:Microsoft® SQL Server®
MySQL
Oracle®
PostgreSQL
For other databases, refer to your database documentation to start a transaction manually. Before running
datainsert
, useexec
to start the transaction.The value of the
AutoCommit
property in theconnection
object determines whetherdatainsert
automatically commits the data to the database.To view the
AutoCommit
value, access it using theconnection
object; for example,conn.AutoCommit
.To set the
AutoCommit
value, use the corresponding name-value pair argument in thedatabase
function.To commit the data to the database, use the
commit
function or issue an SQLCOMMIT
statement using theexec
function.To roll back the data, use
rollback
or issue an SQLROLLBACK
statement using theexec
function.
Alternative Functionality
To export MATLAB data into a database, you can use the fastinsert
and insert
functions. For maximum performance, use
datainsert
.
Version History
Introduced in R2011aR2018a: datainsert
function will be removed
The datainsert
function will be removed in a future release.
Use the sqlwrite
function instead. Some differences between the workflows
require updates to your code.
In prior releases, you exported data from the MATLAB workspace into a database by using the
datainsert
function and four input arguments. For
example:
colnames = {'productNumber','Quantity','Price','inventoryDate'}; data = table(50,100,15.50,{datestr(now,'yyyy-mm-dd HH:MM:SS')}, ... 'VariableNames',colnames); tablename = 'inventoryTable'; datainsert(conn,tablename,colnames,data)
Now the sqlwrite
function requires only three input
arguments.
colnames = {'productNumber','Quantity','Price','inventoryDate'}; data = table(50,100,15.50,{datestr(now,'yyyy-mm-dd HH:MM:SS')}, ... 'VariableNames',colnames); tablename = 'inventoryTable'; sqlwrite(conn,tablename,data)
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)