fastinsert

(To be removed) Add MATLAB data to database tables

The fastinsert function will be removed in a future release. Use the sqlwrite function instead. For details, see Version History.

Syntax

fastinsert(conn,tablename,colnames,data)

Description

fastinsert(conn,tablename,colnames,data) exports data from the MATLAB^® workspace and inserts it into an existing database table using the database connection conn. You can specify the database table name and column names, and specify the data for insertion into the database.

You do not specify the type of data you are exporting. The data is exported in its current MATLAB format.

Examples

Insert Row into Table Using ODBC Driver

First, connect to the Microsoft® SQL Server® database. Then, export data from MATLAB® into the database and close the database connection.

Create a database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, the connection is successful.

conn.Message

ans =

     []

Select and display all rows in the table sorted by the product number using the select function.

selectquery = 'SELECT * FROM productTable ORDER BY productNumber';

data = select(conn,selectquery)

data = 

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

    1                4.0035e+05     1001              14          'Building Blocks' 
    2                4.0031e+05     1002               9          'Painting Set'    
    3                  4.01e+05     1009              17          'Slinky'          

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 that contains these values:

• productNumber equal to 4

• stockNumber equal to 500565

• supplierNumber equal to 1010

• unitCost equal to $20

• productDescription equal to 'Cooking Set'

Then, convert the cell array to a table.

insertdata = {4,500565,1010,20,'Cooking Set'};
insertdata = cell2table(insertdata,'VariableNames',colnames)

insertdata = 

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

    4                5.0057e+05     1010              20          'Cooking Set'     

Insert data into the table.

fastinsert(conn,tablename,colnames,insertdata)

Select and display all rows in the table again.

data = select(conn,selectquery)

data = 

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

    1                4.0035e+05     1001              14          'Building Blocks' 
    2                4.0031e+05     1002               9          'Painting Set'    
    3                  4.01e+05     1009              17          'Slinky'          
    4                5.0057e+05     1010              20          'Cooking Set'     

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

Close the database connection.

close(conn)

Insert Multiple Rows into Table

First, connect to the Microsoft® SQL Server® database. Then, export multiple rows of data from MATLAB® into the database and close the database connection.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, the connection is successful.

conn.Message

ans =

     []

Select and display data in the table inventoryTable. Import data using the select function.

selectquery = 'SELECT * FROM inventoryTable';
data = select(conn,selectquery)

data = 

    productNumber    Quantity    Price    inventoryDate
    _____________    ________    _____    _____________

     1               1700        15       '2014-09-23' 
     2               1200         9       '2014-07-08' 
     3                356        17       '2014-05-14' 
     4               2580        21       '2013-06-08' 
     5               9000         3       '2012-09-14' 
     6               4540         8       '2013-12-25' 
     7               6034        16       '2014-08-06' 
     8               8350         5       '2011-06-18' 
     9               2339        13       '2011-02-09' 
    10                723        24       '2012-03-14' 

Assign multiple rows of data to the cell array insertdata. Each row contains data for the columns in inventoryTable. The first row of data contains:

• Product number is 11

• Quantity is 125

• Price is $23.00

• Inventory date is the current date

insertdata = {11,125,23.00,datestr(now,'yyyy-mm-dd'); ...
    12,1160,14.7,datestr(now,'yyyy-mm-dd'); ...
    13,150,54.5,datestr(now,'yyyy-mm-dd')};

Store the column names of inventoryTable in a cell array.

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

Insert data into the table.

fastinsert(conn,tablename,colnames,insertdata)

Select and display data in the table inventoryTable again.

data = select(conn,selectquery)

data = 

    productNumber    Quantity    Price    inventoryDate
    _____________    ________    _____    _____________

     1               1700        15       '2014-09-23' 
     2               1200         9       '2014-07-08' 
     3                356        17       '2014-05-14' 
     4               2580        21       '2013-06-08' 
     5               9000         3       '2012-09-14' 
     6               4540         8       '2013-12-25' 
     7               6034        16       '2014-08-06' 
     8               8350         5       '2011-06-18' 
     9               2339        13       '2011-02-09' 
    10                723        24       '2012-03-14' 
    11                125        23       '2016-11-02' 
    12               1160        15       '2016-11-02' 
    13                150        55       '2016-11-02' 

Three new rows appear in inventoryTable with data from insertdata.

Close the database connection.

close(conn)

Insert Numeric Data into Table

First, connect to the Microsoft® SQL Server® database. Then, export numeric data from MATLAB® into the database and close the database connection.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

Check the database connection. If the Message property is empty, the connection is successful.

conn.Message

ans =

     []

Define the numeric matrix numdata that contains sales volume data.

numdata = [777666,0,350,400,450,250,450,500,515,235,100,300,600];

Select and display data in the salesVolume table before insertion. Import data using the select function.

selectquery = 'SELECT * FROM salesVolume';
data = select(conn,selectquery)

data = 

    StockNumber    January    February    March    April    May     June    July    August    September    October    November    December
    ___________    _______    ________    _____    _____    ____    ____    ____    ______    _________    _______    ________    ________

    1.2597e+05     1400       1100         981      882      794     752     654    773        809          980        3045       19000   
    2.1257e+05     2400       1721        1414     1191      983     825     731    653        723          790        1400        5000   
    3.8912e+05     1800       1200         890      670      550     450     400    410        402          450        1200       16000   
    4.0031e+05     3000       2400        1800     1500     1200     900     700    650       1670         2500        6000       15000   
    4.0034e+05     4300          0        2600     1800     1600    1550     895    700        750          900        8000       24000   
    4.0035e+05     5000       3500        2800     2300     1700    1400    1000    900       1600         3300       12000       20000   
    4.0046e+05     1200        900         800      500      399     345     300    175        760         1500        5500       17000   
    4.0088e+05     3000       2400        1500     1500     1300    1100     900    867        923         1100        4000       32000   
      4.01e+05     3000       1500        1000      900      750     700     400    350        500         1100        3000       12000   
    8.8865e+05        0        900         821      701      689     621     545    421        495          550        4200       12000   
    4.0814e+05     6000       3100        8800     2300     1700    1400    1000    900       1600         3300       12000       25000   
    2.1046e+05     1800       9700         800      500     3997     349     300    175        760         1500        5500       27000   
    4.7082e+05     3100       9400        1540     1500     1350    1190     900    867        923         1400        3000       35000   
     5.101e+05      235       1800        1040      900      750     700     400    350        500          100        3000       18000   
    8.9975e+05      123       1700         823      701      689     621     545    421        495          650        4200       11000   

Store the column names of salesVolume in a cell array.

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

Insert data into the table.

fastinsert(conn,tablename,colnames,numdata)

Select and display data in the salesVolume table again.

data = select(conn,selectquery)

data = 

    StockNumber    January    February    March    April    May     June    July    August    September    October    November    December
    ___________    _______    ________    _____    _____    ____    ____    ____    ______    _________    _______    ________    ________

    1.2597e+05     1400       1100         981      882      794     752     654    773        809          980        3045       19000   
    2.1257e+05     2400       1721        1414     1191      983     825     731    653        723          790        1400        5000   
    3.8912e+05     1800       1200         890      670      550     450     400    410        402          450        1200       16000   
    4.0031e+05     3000       2400        1800     1500     1200     900     700    650       1670         2500        6000       15000   
    4.0034e+05     4300          0        2600     1800     1600    1550     895    700        750          900        8000       24000   
    4.0035e+05     5000       3500        2800     2300     1700    1400    1000    900       1600         3300       12000       20000   
    4.0046e+05     1200        900         800      500      399     345     300    175        760         1500        5500       17000   
    4.0088e+05     3000       2400        1500     1500     1300    1100     900    867        923         1100        4000       32000   
      4.01e+05     3000       1500        1000      900      750     700     400    350        500         1100        3000       12000   
    8.8865e+05        0        900         821      701      689     621     545    421        495          550        4200       12000   
    4.0814e+05     6000       3100        8800     2300     1700    1400    1000    900       1600         3300       12000       25000   
    2.1046e+05     1800       9700         800      500     3997     349     300    175        760         1500        5500       27000   
    4.7082e+05     3100       9400        1540     1500     1350    1190     900    867        923         1400        3000       35000   
     5.101e+05      235       1800        1040      900      750     700     400    350        500          100        3000       18000   
    8.9975e+05      123       1700         823      701      689     621     545    421        495          650        4200       11000   
    7.7767e+05        0        350         400      450      250     450     500    515        235          100         300         600   

A new row appears in salesVolume with data from numdata.

Close the database connection.

close(conn)

Insert and Commit Data in Table

First, connect to the Microsoft® SQL Server® database. Then, export data from MATLAB® into the database and commit the insert transaction. Close the database connection.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. Use the name-value pair argument AutoCommit to specify manually committing transactions to the database.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','','AutoCommit','off');

Check the database connection. If the Message property is empty, the connection is successful.

conn.Message

ans =

     []

Insert the cell array data into the table 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 insert transaction.

commit(conn)

Close the database connection.

close(conn)

Insert Boolean Data into Table

First, connect to the Microsoft® SQL Server® database. Then, export Boolean data from MATLAB® into the database. Close the database connection.

Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password.

datasource = 'MS SQL Server Auth';
conn = database(datasource,'','');

This database contains the table Invoice with these columns:

• InvoiceNumber

• InvoiceDate

• productNumber

• Paid

• Receipt

Check the database connection. If the Message property is empty, then the connection is successful.

conn.Message

ans =

     []

Display the data in the Invoice table before insertion.

selectquery = 'SELECT * FROM Invoice';
data = select(conn,selectquery)

data =

  10×5 table

    InvoiceNumber           InvoiceDate           ProductNumber    Paid        Receipt    
    _____________    _________________________    _____________    _____    ______________

     2101            '2010-08-01 00:00:00.000'     1               false    [8000×1 uint8]
     3546            '2010-03-01 00:00:00.000'     2               true     [8000×1 uint8]
    33116            '2011-05-15 00:00:00.000'     3               true     [8000×1 uint8]
    34155            '2011-07-12 00:00:00.000'     4               false    [8000×1 uint8]
    34267            '2011-07-22 00:00:00.000'     5               true     [8000×1 uint8]
    37197            '2011-09-03 00:00:00.000'     6               true     [8000×1 uint8]
    37281            '2011-09-21 00:00:00.000'     7               false    [8000×1 uint8]
    41011            '2011-12-12 00:00:00.000'     8               true     [8000×1 uint8]
    61178            '2012-01-15 00:00:00.000'     9               false    [8000×1 uint8]
    62145            '2012-01-23 00:00:00.000'    10               true     [8000×1 uint8]

Create the variable insertdata as a structure containing the invoice number 2105, product number 11, and the Boolean data false to signify unpaid. Boolean data is represented as the MATLAB® data type logical. This code assumes that the receipt image is missing.

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

Insert the paid invoice data into the Invoice table with column names colnames using the database connection.

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

fastinsert(conn,tablename,colnames,insertdata)

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

data = select(conn,selectquery)

data =

  11×5 table

    InvoiceNumber           InvoiceDate           ProductNumber    Paid        Receipt    
    _____________    _________________________    _____________    _____    ______________

     2101            '2010-08-01 00:00:00.000'     1               false    [8000×1 uint8]
     3546            '2010-03-01 00:00:00.000'     2               true     [8000×1 uint8]
    33116            '2011-05-15 00:00:00.000'     3               true     [8000×1 uint8]
    34155            '2011-07-12 00:00:00.000'     4               false    [8000×1 uint8]
    34267            '2011-07-22 00:00:00.000'     5               true     [8000×1 uint8]
    37197            '2011-09-03 00:00:00.000'     6               true     [8000×1 uint8]
    37281            '2011-09-21 00:00:00.000'     7               false    [8000×1 uint8]
    41011            '2011-12-12 00:00:00.000'     8               true     [8000×1 uint8]
    61178            '2012-01-15 00:00:00.000'     9               false    [8000×1 uint8]
    62145            '2012-01-23 00:00:00.000'    10               true     [8000×1 uint8]
     2105            '2017-01-04 10:19:42.000'    11               false    ''            

The last row contains the Boolean data false.

Close the database connection.

close(conn)

Input Arguments

conn — Database connection

connection object

Database connection, specified as an ODBC connection object or JDBC connection object created using the database function.

tablename — Database table name

string scalar | character vector

Database table name, specified as a string scalar or character vector denoting the name of a table in the database.

Example: "employees"

Data Types: string | char

colnames — Database table column names

cell array of character vectors | string array

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

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

Data Types: cell | string

data — Data to insert

numeric matrix | cell array | table | dataset | structure

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, then field names in the structure must match colnames. If data is a table or a dataset array, then 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 reduce conversion time, convert dates to serial date numbers using datenum before calling fastinsert.

Tips

• The value of the AutoCommit property in the connection object determines whether fastinsert automatically commits the data to the database.

  • To view the AutoCommit value, access it using the connection object; for example, conn.AutoCommit.

  • To set the AutoCommit value, use the corresponding name-value pair argument in the database function.

  • To commit the data to the database, use the commit function or issue an SQL COMMIT statement using the exec function.

  • To roll back the data, use rollback or issue an SQL ROLLBACK statement using the exec function.

• 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.

Alternative Functionality

To export MATLAB data into a database, you can use the datainsert and insert functions. For maximum performance, use datainsert.

Version History

Introduced before R2006a

R2018a: fastinsert function will be removed

The fastinsert function will be removed in a future release. Use the sqlwrite function instead. Some differences between the workflows require updates to your code.

Update Code

In prior releases, you exported data from the MATLAB workspace into a database by using the fastinsert function and four input arguments. For example:

tablename = 'productTable';
colnames = {'productNumber','stockNumber','supplierNumber', ...
    'unitCost','productDescription'};
insertdata = {4,500565,1010,20,'Cooking Set'};
insertdata = cell2table(insertdata,'VariableNames',colnames)
fastinsert(conn,tablename,colnames,insertdata)

Now the sqlwrite function requires only three input arguments.

tablename = 'productTable';
colnames = {'productNumber','stockNumber','supplierNumber', ...
    'unitCost','productDescription'};
insertdata = {4,500565,1010,20,'Cooking Set'};
insertdata = cell2table(insertdata,'VariableNames',colnames)
sqlwrite(conn,tablename,insertdata)