Documentation

This is machine translation

Translated by Microsoft
Mouse over text to see original. Click the button below to return to the English verison of the page.

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.

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

Create a database connection conn to the Microsoft® Access™ database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with admin as the user name and password. This database contains the table productTable with these columns:

  • productNumber

  • stockNumber

  • supplierNumber

  • unitCost

  • productDescription

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

  connection 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. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = 'select * from productTable';

curs = exec(conn,sqlquery);
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'    

After you finish working with the cursor object, close it.

close(curs)

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.

sqlquery = 'select * from productTable';

curs = exec(conn,sqlquery);
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.

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Create a database connection conn to the Microsoft Access database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with admin as the user name and password. 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. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = 'select * from inventoryTable';

curs = exec(conn,sqlquery);
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...'

After you finish working with the cursor object, close it.

close(curs)

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.

sqlquery = 'select * from inventoryTable';

curs = exec(conn,sqlquery);
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.

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Create a database connection conn to the Microsoft Access database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with admin as the user name and password. 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. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = 'select * from inventoryTable';

curs = exec(conn,sqlquery);
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...'

After you finish working with the cursor object, close it.

close(curs)

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.

sqlquery = 'select * from inventoryTable';

curs = exec(conn,sqlquery);
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.

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Create a database connection conn to the Microsoft Access database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with admin as the user name and password. 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. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = 'select March from salesVolume';

curs = exec(conn,sqlquery);
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 insert; 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.

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Create a database connection conn to the Microsoft Access database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with admin as the user name and password. 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. The cursor object curs contains the executed query. Import the data from the executed query using the fetch function.

sqlquery = 'select * from salesVolume';

curs = exec(conn,sqlquery);
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]

After you finish working with the cursor object, close it.

close(curs)

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.

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

Create a database connection conn to the Microsoft Access database. For example, the following code assumes that you are connecting to a data source named dbtoolboxdemo with admin as the user name and password. 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. The cursor object curs contains the executed query.

curs = exec(conn,'commit');

After you finish working with the cursor object, close it. Close the database connection.

close(curs)
close(conn)

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.

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Related Examples

Input Arguments

collapse all

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

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

Data Types: char

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 to insert, 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.

To insert data into a structure, table, or dataset array, use this special formatting. Each field or variable in a structure, table, or dataset array must be a cell array or double vector. The double vector must be of size n-by-1, where n is the number of rows to be inserted.

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

To reduce conversion time, convert dates to serial date numbers using datenum before calling fastinsert.

More About

collapse all

Tips

  • The status of the AutoCommit flag determines whether fastinsert automatically commits the data to the database. Use get to view the AutoCommit flag status for the connection and use set to change it. Use commit or issue an SQL COMMIT statement using exec to commit the data to the database. Use rollback or issue an SQL ROLLBACK statement using exec to roll back the data.

  • If an error message like the following appears when you run fastinsert, the table might be open in edit mode.

    [Vendor][ODBC Product Driver] The database engine could 
    not lock table 'TableName' because it is already in use 
    by another person or process.
    

    In this case, close the table in the database and rerun the fastinsert function.

Introduced before R2006a

Was this topic helpful?