fastinsert

Add MATLAB data to database table

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.

  • 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

Insert a Table Record Using Native ODBC

Create a connection conn using the native ODBC interface and the dbtoolboxdemo data source.

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
    -------------    -----------    --------------    --------    ------------------
     9               125970         1003              13          'Victorian Doll'  
     8               212569         1001               5          'Train Set'       
     7               389123         1007              16          'Engine Kit'      
     2               400314         1002               9          'Painting Set'    
     4               400339         1008              21          'Space Cruiser'   
     1               400345         1001              14          'Building Blocks' 
     5               400455         1005               3          'Tin Soldier'     
     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 a cell array, data. The data contains productNumber equal to 11, stockNumber equal to 500565, supplierNumber equal to 1010, unitCost equal to $20, and productDescription equal to 'Cooking Set'. Then, convert the cell array to a 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
    -------------    -----------    --------------    --------    ------------------
     9               125970         1003              13          'Victorian Doll'  
     8               212569         1001               5          'Train Set'       
     7               389123         1007              16          'Engine Kit'      
     2               400314         1002               9          'Painting Set'    
     4               400339         1008              21          'Space Cruiser'   
     1               400345         1001              14          'Building Blocks' 
     5               400455         1005               3          'Tin Soldier'     
     6               400876         1004               8          'Sail Boat'       
     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 database connection.

close(conn)

Insert a Record

Using the dbtoolboxdemo data source, establish the database connection conn using database.ODBCConnection or database. Assign the data to the cell array data. The data for insertion is productNumber equals 7777, Quantity equals 100, and Price equals 50.00.

data = {7777,100,50.00};

Create a cell array containing the column names of three columns, productNumber, Quantity, and Price.

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

Insert the data into the inventoryTable.

fastinsert(conn,tablename,colnames,data)

Close the database connection.

close(conn)

Insert Multiple Records

Using the dbtoolboxdemo data source, establish the database connection conn using database.ODBCConnection or database. Assign multiple rows of data to the cell array data. Each row contains data for productNumber, Quantity, and Price. For example, the first row data for insertion is productNumber equals 7778, Quantity equals 125, and Price equals 23.00.

data = {7778,125,23.00; 7779,1160,14.7; 7780,150,54.5};

Create a cell array containing the column names of three columns, productNumber, Quantity, and Price.

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

Insert the data into the inventoryTable.

fastinsert(conn,tablename,colnames,data)

For details, there are three 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

Close the database connection.

close(conn)

Import Records, Perform Calculations, and Export Data

This example shows how to retrieve sales data from a salesVolume table, calculate the sum of sales for 1 month, store this data in a cell array, and export this data to a yearlySales table.

Connect to the data source dbtoolboxdemo.

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 current 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

Using the dbtoolboxdemo data source, establish the database connection conn using database.ODBCConnection or database. Then, insert data, a numeric matrix consisting of three columns denoted by colnames, into the inventoryTable table.

data = [25,439,60.00];
tablename = 'inventoryTable';
colnames = {'productNumber','Quantity','Price'};
fastinsert(conn,tablename,colnames,data)

Close the database connection.

close(conn)

Insert and Commit Data

Using the dbtoolboxdemo data source, establish the database connection conn using database.ODBCConnection or database. Then, 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};
colnames = {'productNumber','Quantity','Price'};
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 database connection.

close(conn)

Insert Boolean Data

Using the dbtoolboxdemo data source, insert BOOLEAN data (which is represented as MATLAB type logical) into a database.

Connect to the data source dbtoolboxdemo.

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

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

Create data as a structure containing the invoice number 2101 and the BOOLEAN data of 1 to signify paid.

data.InvoiceNumber{1} = 2101;
data.Paid{1} = logical(1);

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

colnames = {'InvoiceNumber';'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 is shown as a BOOLEAN false, No, or a cleared check box.

Close the database 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 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?