Documentation

datainsert

Export MATLAB data into database table

To export MATLAB® data into a database, use these functions: datainsert, fastinsert, and insert. For maximum performance, use datainsert.

For other differences among these functions, see Inserting Data Using the Command Line.

Syntax

  • datainsert(conn,tablename,colnames,data)
    example

Description

example

datainsert(conn,tablename,colnames,data) inserts data from the MATLAB workspace into a database table.

Examples

collapse all

Export MATLAB Cell Array Data

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 user name username and password pwd. This database contains the table inventoryTable with these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

conn = database.ODBCConnection('MySQL','username','pwd');

Display the last rows in inventoryTable before insertion of 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 cell array of column names for the database table inventoryTable.

colnames = {'productNumber','Quantity','Price','inventoryDate'};

Define the 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 database connection conn.

tablename = 'inventoryTable';

datainsert(conn,tablename,colnames,data) 

Display 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 connection.

close(conn)

Export MATLAB Table Data

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 user name username and password pwd. 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 insertion of 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 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 database connection conn.

tablename = 'inventoryTable';

datainsert(conn,tablename,colnames,data) 

Display 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 connection.

close(conn)

Export MATLAB Structure Data

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 user name username and password pwd. This database contains the table inventoryTable with these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

conn = database.ODBCConnection('MySQL','username','pwd');

Display the last rows in inventoryTable before insertion of 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 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 database connection conn.

tablename = 'inventoryTable';

datainsert(conn,tablename,colnames,data) 

Display 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 connection.

close(conn)

Export MATLAB Numeric Matrix Data

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 user name username and password pwd. 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 database connection conn.

tablename = 'salesVolume';

datainsert(conn,tablename,colnames,data) 

Display 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 connection.

close(conn)

Related Examples

Input Arguments

collapse all

conn — Database connectiondatabase connection object

Database connection, specified as a database connection object created using database.

tablename — Database table namecharacter vector

Database table name, specified as a character vector denoting the name of a table in your database.

Data Types: char

colnames — Database table column namescell array of character vectors

Database table column names, specified as a cell array of one or more character vectors to denote the columns in the existing database table tablename.

Example: {'col1','col2','col3'}

Data Types: cell

data — Insert datacell 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 or the JDBC/ODBC bridge, 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 NaNs, convert these entries to an empty value ''.

MATLAB date numbers and NaNs are supported for insert when data is a numeric matrix. Date numbers inserted into database date and time columns convert to java.sql.Date. Any converted date and time data is accurately converted back to the native database format in the target database upon insertion.

If data is a structure, field names in the structure must match colnames. If data is a table or a dataset array, the variable names in the table or dataset array must match colnames.

More About

collapse all

Tips

  • When you establish a database connection using a JDBC driver or the JDBC/ODBC bridge, datainsert performs faster than fastinsert.

  • To insert dates and timestamps with the native ODBC interface, use the format 'YYYY-MM-DD HH:MM:SS.MS'.

  • datainsert uses the SQL TRANSACTION 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, use exec to start the transaction.

  • The status of the AutoCommit flag determines whether datainsert commits the data to the database. View the status of this flag using get. To change the status of this flag, use set. To commit the insert data to the database, use commit or run an SQL COMMIT statement using exec. To roll back the insert data from the database, use rollback or run an SQL ROLLBACK statement using exec.

Introduced in R2011a

Was this topic helpful?