insert

(To be removed) Add MATLAB data to database tables

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

Syntax

insert(conn,tablename,colnames,data)

Description

insert(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.

If conn is a JDBC database connection, then the insert function has the same functionality as the fastinsert function.

Examples

Insert Table Record Using Native ODBC

Create an ODBC database connection to the Microsoft Access^® database. This code assumes that you are connecting to a data source named dbdemo with admin as the user name and password.

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

This database contains the table producttable with these columns:

• productnumber

• stocknumber

• suppliernumber

• unitcost

• productdescription

Select and display the data from the producttable table. The cursor object contains the executed query. Import the data from the executed query using the fetch function.

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.

colnames = {'productnumber','stocknumber','suppliernumber',...
    'unitcost','productdescription'};

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

• productnumber equal to 11

• stocknumber equal to 400565

• suppliernumber equal to 1010

• unitcost equal to $10

• productdescription equal to 'Rubik''s Cube'

Then, convert the cell array to the table data_table.

data = {11,400565,1010,10,'Rubik''s Cube'};
data_table = cell2table(data,'VariableNames',colnames)

data_table = 

    productnumber    stocknumber    suppliernumber    unitcost    productdescription
    -------------    -----------    --------------    --------    ------------------
    11               400565         1010              10          'Rubik's Cube'      

Insert the table data into producttable.

tablename = 'producttable';
insert(conn,tablename,colnames,data_table)

Display the data from 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               400565         1010              10          'Rubik's Cube'    

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

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

close(curs)

Close the database connection.

close(conn)

Input Arguments

conn — SQLite database connection

sqlite object

SQLite database connection, specified as an sqlite object created using the sqlite 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 — Insert data

cell array | numeric matrix | table | dataset | structure

Insert data, specified as a cell array, numeric matrix, table, dataset array, or structure. These values depend on the type of database connection.

For a connection object, you do not specify the type of data that you are exporting. The insert function exports the data in its current MATLAB format. 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. If data is a structure, table, or dataset array, then specify each field or variable as a:

• Cell array

• Double vector of size m-by-1, where m is the number of rows to insert

For a sqlite object, the dataset array is not supported. Only double, int64, and char data types are supported.

Alternative Functionality

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

Version History

Introduced before R2006a

R2022a: insert function will be removed

The insert 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 specified a cell array when exporting data from the MATLAB workspace into a database. For example:

colnames = {'month','salestotal','revenue'};
data = {'March',50,2000};
tablename = 'yearlysales';
insert(conn,tablename,colnames,data)

Now the sqlwrite function requires you to specify the data to export as a table.

colnames = {'month','salestotal','revenue'};
d = {'March',50,2000};
data = cell2table(d,'VariableNames',colnames);
tablename = 'yearlysales';
sqlwrite(conn,tablename,data)