Documentation

fastinsert

Add MATLAB data to database table

To export MATLAB® data into a database, use these functions: fastinsert, datainsert, 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

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

Description

example

fastinsert(conn,tablename,colnames,data) exports records from the MATLAB variable data into new rows in an existing database table tablename and in existing columns colnames using the connection conn. You do not specify the type of data you are exporting; the data is exported in its current MATLAB format.

Examples

collapse all

Insert a Table Record Using Native ODBC

Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source. This data source identifies a Microsoft® Access™ database. This database contains the table productTable with these columns:

  • productNumber

  • stockNumber

  • supplierNumber

  • unitCost

  • productDescription

conn = database.ODBCConnection('dbtoolboxdemo','admin','admin')
conn = 

  ODBCConnection with properties:

      Instance: 'dbtoolboxdemo'
      UserName: 'admin'
       Message: []
        Handle: [1x1 database.internal.ODBCConnectHandle]
       TimeOut: 0
    AutoCommit: 0
          Type: 'ODBCConnection Object'

conn has an empty Message property, which means a successful connection.

Select and display the data from the productTable.

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

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    -------------    -----------    --------------    --------    ------------------
    ...
     6               400876         1004               8          'Sail Boat'       
     3               400999         1009              17          'Slinky'          
    10               888652         1006              24          'Teddy Bear'    

Store the column names of productTable in a cell array.

tablename = 'productTable';
colnames = {'productNumber','stockNumber','supplierNumber',...
					'unitCost','productDescription'};

Store the data for the insert in the cell array data that contains these values:

  • productNumber equal to 11

  • stockNumber equal to 500565

  • supplierNumber equal to 1010

  • unitCost equal to $20

  • productDescription equal to 'Cooking Set'

Then, convert the cell array to the table data_table.

data = {11,500565,1010,20,'Cooking Set'};
data_table = cell2table(data,'VariableNames',colnames)
data_table = 

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    -------------    -----------    --------------    --------    ------------------
    11               500565         1010              20          'Cooking Set'     

Insert the table data into the productTable.

fastinsert(conn,tablename,colnames,data_table)

Display the data from the productTable again.

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

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    -------------    -----------    --------------    --------    ------------------
    ...
     3               400999         1009              17          'Slinky'          
    10               888652         1006              24          'Teddy Bear'      
    11               500565         1010              20          'Cooking Set'     

A new row appears in the productTable with the data from data_table.

Close the cursor and database connection.

close(curs)
close(conn)

Insert a Record

Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table inventoryTable with these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

conn = database('dbtoolboxdemo','admin','admin');

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

Display the data in the inventoryTable table before insertion.

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

    ...
    [11]    [  567]    [      0]    '2012-09-11 00:30...'
    [12]    [ 1278]    [      0]    '2010-10-29 18:17...'
    [13]    [ 1700]    [14.5000]    '2009-05-24 10:58...'

Assign the data for insertion to the cell array data. The data is:

  • productNumber is 7777

  • Quantity is 100

  • Price is 50.00

  • inventoryDate is the date of the current moment

data = {7777,100,50.00,datestr(now,'yyyy-mm-dd HH:MM:SS')};

Create a cell array containing these column names: productNumber, Quantity, Price, inventoryDate.

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

Insert the data into the inventoryTable.

fastinsert(conn,tablename,colnames,data)

Display the data in the inventoryTable table after insertion.

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

    ...
    [  12]    [ 1278]    [      0]    '2010-10-29 18:17...'
    [  13]    [ 1700]    [14.5000]    '2009-05-24 10:58...'
    [7777]    [  100]    [     50]    '2014-10-23 10:01...'

The last row contains the inserted data.

Close the cursor and database connection.

close(curs)
close(conn)

Insert Multiple Records

Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table inventoryTable with these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

conn = database('dbtoolboxdemo','admin','admin');

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

Display the data in the inventoryTable table before insertion.

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

    ...
    [11]    [  567]    [      0]    '2012-09-11 00:30...'
    [12]    [ 1278]    [      0]    '2010-10-29 18:17...'
    [13]    [ 1700]    [14.5000]    '2009-05-24 10:58...'

Assign multiple rows of data to the cell array data. Each row contains data for productNumber, Quantity, and Price. The first row data for insertion is productNumber equals 7778, Quantity equals 125, and Price equals 23.00.

data = {7778,125,23.00,datestr(now,'yyyy-mm-dd HH:MM:SS');...
        7779,1160,14.7,datestr(now,'yyyy-mm-dd HH:MM:SS');...
        7780,150,54.5,datestr(now,'yyyy-mm-dd HH:MM:SS')};

Create a cell array containing these column names:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

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

Insert the data into the inventoryTable.

fastinsert(conn,tablename,colnames,data)

For details about inserting data in bulk, see these sample files for different database vendors that demonstrate bulk insert:

  • matlabroot/toolbox/database/dbdemos/mssqlserverbulkinsert.m

  • matlabroot/toolbox/database/dbdemos/mysqlbulkinsert.m

  • matlabroot/toolbox/database/dbdemos/oraclebulkinsert.m

Display the data in the inventoryTable table after insertion.

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

    ...
    [  13]    [ 1700]    [14.5000]    '2009-05-24 10:58...'
    [7778]    [  125]    [     23]    '2014-10-23 10:21...'
    [7779]    [ 1160]    [14.7000]    '2014-10-23 10:21...'
    [7780]    [  150]    [54.5000]    '2014-10-23 10:21...'

The last three rows contain the inserted data.

Close the cursor and database connection.

close(curs)
close(conn)

Import Records, Perform Calculations, and Export Data

Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the tables salesVolume and yearlySales.

conn = database('dbtoolboxdemo','admin','admin');

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

Use setdbprefs to set the format for retrieved data to numeric.

setdbprefs('DataReturnFormat','numeric')

Import 10 rows of data from the March column in the salesVolume table.

curs = exec(conn,'select March from salesVolume');
curs = fetch(curs);

Assign the data to the MATLAB workspace variable AA.

AA = curs.Data
AA =

981
1414
890
1800
2600
2800
800
1500
1000
821

Calculate the sum of the March sales and assign the result to the variable sumA.

sumA = sum(AA(:))
sumA =
   
   14606

Assign the month and sum of sales to a cell array to export to a database. Put the month in the first cell of data.

data(1,1) = {'March'}
data = 
		'March'

Put the sum in the second cell of data.

data(1,2) = {sumA}
data = 
    'March'    [14606]

Define the names of the columns to which to export data. In this example, the column names are Month and salesTotal, from the yearlySales table in the dbtoolboxdemo database. Assign the cell array containing the column names to the variable colnames.

tablename = 'yearlySales';
colnames = {'Month','salesTotal'};

Access the status of the AutoCommit database flag. This status determines whether the exported data is automatically committed to the database. If the flag is off, you can undo an update; if it is on, data is automatically committed to the database.

conn.AutoCommit
ans =
	on

The AutoCommit flag is set to on, so the exported data is automatically committed to the database.

Use fastinsert to export the data into the yearlySales table.

fastinsert(conn,tablename,colnames,data)

fastinsert appends the data as a new record at the end of the yearlySales table.

In Microsoft Access, view the yearlySales table to verify the results.

Close the cursor and database connection.

close(curs)
close(conn)

Insert Numeric Data

Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table salesVolume. This table contains the column stockNumber and columns for the months of the year.

conn = database('dbtoolboxdemo','admin','admin');

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

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];

Display the data in the salesVolume table before insertion.

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

  Columns 1 through 8

    ...
    [400876]    [3000]    [2400]    [1500]    [1500]    [1300]    [1100]    [ 900]
    [400999]    [3000]    [1500]    [1000]    [ 900]    [ 750]    [ 700]    [ 400]
    [888652]    [ NaN]    [ 900]    [ 821]    [ 701]    [ 689]    [ 621]    [ 545]

  Columns 9 through 13

    ...
    [867]    [ 923]    [1100]    [ 4000]    [32000]
    [350]    [ 500]    [1100]    [ 3000]    [12000]
    [421]    [ 495]    [ 550]    [ 4200]    [12000]

Insert data using the columns denoted by colnames into the salesVolume table.

tablename = 'salesVolume';
colnames = {'stockNumber','January','February'...
            'March','April','May',...
            'June','July','August',...
            'September','October','November',...
            'December'};

fastinsert(conn,tablename,colnames,data)

Display the data in the salesVolume table after insertion.

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

  Columns 1 through 8

    ...
    [400999]    [3000]    [1500]    [1000]    [ 900]    [ 750]    [ 700]    [ 400]
    [888652]    [ NaN]    [ 900]    [ 821]    [ 701]    [ 689]    [ 621]    [ 545]
    [777666]    [   0]    [ 350]    [ 400]    [ 450]    [ 250]    [ 450]    [ 500]

  Columns 9 through 13

    ...
    [350]    [ 500]    [1100]    [ 3000]    [12000]
    [421]    [ 495]    [ 550]    [ 4200]    [12000]
    [515]    [ 235]    [ 100]    [  300]    [  600]

The last row contains the inserted data.

Close the cursor and database connection.

close(curs)
close(conn)

Insert and Commit Data

Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table inventoryTable with these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

conn = database('dbtoolboxdemo','admin','admin');

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

Set the AutoCommit flag to off.

set(conn,'AutoCommit','off')

Insert the cell array data into the inventoryTable with column names colnames.

data = {157,358,740.00,datestr(now,'yyyy-mm-dd HH:MM:SS')};
colnames = {'productNumber','Quantity','Price','inventoryDate'};
tablename = 'inventoryTable';

fastinsert(conn,tablename,colnames,data)

Commit the inserted data.

commit(conn)

Alternatively, commit the data using an SQL commit statement with the exec function.

curs = exec(conn,'commit');

Close the cursor and database connection.

close(curs)
close(conn)

Insert Boolean Data

Connect to the data source dbtoolboxdemo. This data source identifies a Microsoft Access database. This database contains the table invoice with these columns:

  • InvoiceNumber

  • InvoiceDate

  • productNumber

  • Paid

  • Receipt

conn = database('dbtoolboxdemo','admin','admin');

Alternatively, you can use the native ODBC interface for an ODBC connection. For details, see database.

Display the data in the invoice table before insertion.

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

    ...
    [41011]    '2011-12-12 00:00...'    [ 8]    [1]    [1920474x1 int8]
    [61178]    '2012-01-15 00:00...'    [ 9]    [0]    [2378330x1 int8]
    [62145]    '2012-01-23 00:00...'    [10]    [1]    [ 492314x1 int8]

Create data as a structure containing the invoice number 2101 and the Boolean data 1 to signify paid. Boolean data is represented as the MATLAB type logical. Here, assume that the receipt image is missing.

data.InvoiceNumber{1} = 2101;
data.InvoiceDate{1} = datestr(now,'yyyy-mm-dd HH:MM:SS');
data.productNumber{1} = 11;
data.Paid{1} = logical(1);

Insert the paid invoice data into the invoice table with column names colnames.

colnames = {'InvoiceNumber';'InvoiceDate';'productNumber';'Paid'};
tablename = 'invoice';

fastinsert(conn,tablename,colnames,data)

View the new record in the database to verify that the Paid field is Boolean. In some databases, the MATLAB logical value 0 shows as a Boolean false, No, or a cleared check box.

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

    ...
    [61178]    '2012-01-15 00:00...'    [ 9]    [0]    [2378330x1 int8]
    [62145]    '2012-01-23 00:00...'    [10]    [1]    [ 492314x1 int8]
    [ 2101]    '2014-10-23 11:14...'    [11]    [1]                  []

The last row contains the Boolean data 1.

Close the database 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 datanumeric matrix | cell array | table | dataset | structure

Insert data, specified as a numeric matrix, cell array, table, dataset array, or structure, that contains all data for insertion into the existing database table tablename. 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.

Data Types: double | cell | table | struct

Was this topic helpful?