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. To use datainsert, ensure that your input data is a cell array or numeric matrix with a specific format. When your input data is a structure, dataset array, or table, or you are using a native ODBC database connection, use fastinsert. If datainsert or fastinsert do not work for you and you want to insert a small set of data, use insert.

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

Establish the connection conn to a MySQL® database with the user name username and password pwd. 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 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.

Close the connection.

close(conn)

Export MATLAB Numeric Matrix Data

Establish connection conn to a MySQL database 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('MySQL','username','pwd');

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.

Close the connection.

close(conn)

Input Arguments

collapse all

conn — Database connectiondatabase connection object

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

tablename — Database table namestring

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

Data Types: char

colnames — Database table column namescell array of strings

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

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

Data Types: cell

data — Insert datacell array | numeric matrix

Insert data, specified as a MATLAB cell array or numeric matrix. If data is a cell array containing MATLAB dates, times, or timestamps, use this formatting:

  • Dates must be date strings of the form yyyy-mm-dd.

  • Times must be time strings of the form HH:MM:SS.

  • Timestamps must be strings of the form yyyy-mm-dd HH:MM:SS.FFF.

Any null entries and any NaNs in the cell array must be converted to empty strings before calling datainsert. 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.

Data Types: double | cell

Was this topic helpful?