datainsert

Export MATLAB data into database table

Syntax

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

Description

example

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

  • Use datainsert when you want maximum performance, are able to format your input data in a specific way, and your input data is only cell arrays and numeric matrices.

  • Use fastinsert when your input data is a structure, dataset array, or table, or you are using a native ODBC database connection.

  • Use insert only if datainsert or fastinsert do not work for you and you want to insert a small set of data.

Examples

expand all

Export MATLAB Cell Array Data

Insert data in a MATLAB cell array into a database.

Establish the connection conn to a MySQL® database with the user name username and password pwd.

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

Display data in inventoryTable before insertion of data.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [   1]    [1700]    [14.5000]
    [   2]    [1200]    [ 9.3000]
    [   3]    [ 356]    [17.2000]
    [   4]    [2580]    [21.4000]
    [   5]    [9000]    [ 3.0500]
    [   6]    [4540]    [ 8.1000]
    [   7]    [6034]    [16.2000]
    [   8]    [8350]    [ 5.1000]
    [   9]    [2339]    [13.2000]
    [  10]    [ 723]    [24.3000]
    [  11]    [ 567]    [11.2000]
    [  12]    [1278]    [22.3000]
    [  13]    [1700]    [16.8000]
    [  14]    [2000]    [19.1000]
    [  15]    [1200]    [20.3000]
    [7777]    [ 100]    [     50]
    [7777]    [ 100]    [     50]
    [8888]    [ 200]    [    101]

Create cell array of column names for the database table inventoryTable.

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

Define the cell array of input data to insert.

data = {50 100 15.50};

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 = 

    [   1]    [1700]    [14.5000]
    [   2]    [1200]    [ 9.3000]
    [   3]    [ 356]    [17.2000]
    [   4]    [2580]    [21.4000]
    [   5]    [9000]    [ 3.0500]
    [   6]    [4540]    [ 8.1000]
    [   7]    [6034]    [16.2000]
    [   8]    [8350]    [ 5.1000]
    [   9]    [2339]    [13.2000]
    [  10]    [ 723]    [24.3000]
    [  11]    [ 567]    [11.2000]
    [  12]    [1278]    [22.3000]
    [  13]    [1700]    [16.8000]
    [  14]    [2000]    [19.1000]
    [  15]    [1200]    [20.3000]
    [7777]    [ 100]    [     50]
    [7777]    [ 100]    [     50]
    [8888]    [ 200]    [    101]
    [  50]    [ 100]    [15.5000]

Close the connection.

close(conn)

Export MATLAB Numeric Matrix Data

Insert data in a MATLAB numeric matrix into a database.

Establish connection conn to a MySQL database with user name username and password pwd.

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

Display data in inventoryTable before inserting data.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [   1]    [1700]    [14.5000]
    [   2]    [1200]    [ 9.3000]
    [   3]    [ 356]    [17.2000]
    [   4]    [2580]    [21.4000]
    [   5]    [9000]    [ 3.0500]
    [   6]    [4540]    [ 8.1000]
    [   7]    [6034]    [16.2000]
    [   8]    [8350]    [ 5.1000]
    [   9]    [2339]    [13.2000]
    [  10]    [ 723]    [24.3000]
    [  11]    [ 567]    [11.2000]
    [  12]    [1278]    [22.3000]
    [  13]    [1700]    [16.8000]
    [  14]    [2000]    [19.1000]
    [  15]    [1200]    [20.3000]
    [7777]    [ 100]    [     50]
    [7777]    [ 100]    [     50]
    [8888]    [ 200]    [    101]
    [  50]    [ 100]    [15.5000]

Create a cell array of column names for the database table inventoryTable.

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

Define the numeric matrix of input data to insert.

data = [55 200 20.50];

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 = 

    [   1]    [1700]    [14.5000]
    [   2]    [1200]    [ 9.3000]
    [   3]    [ 356]    [17.2000]
    [   4]    [2580]    [21.4000]
    [   5]    [9000]    [ 3.0500]
    [   6]    [4540]    [ 8.1000]
    [   7]    [6034]    [16.2000]
    [   8]    [8350]    [ 5.1000]
    [   9]    [2339]    [13.2000]
    [  10]    [ 723]    [24.3000]
    [  11]    [ 567]    [11.2000]
    [  12]    [1278]    [22.3000]
    [  13]    [1700]    [16.8000]
    [  14]    [2000]    [19.1000]
    [  15]    [1200]    [20.3000]
    [7777]    [ 100]    [     50]
    [7777]    [ 100]    [     50]
    [8888]    [ 200]    [    101]
    [  50]    [ 100]    [15.5000]
    [  55]    [ 200]    [20.5000]

Close the connection.

close(conn)

Input Arguments

expand all

conn — Database connectionconnection 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, the dates must be date strings of the form yyyy-mm-dd, times must be time strings of the form HH:MM:SS, and 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

See Also

| |

Was this topic helpful?