insert

(To be removed) Add MATLAB data to database tables

collapse all in page

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

Syntax

insert(conn,tablename,colnames,data)

Description

example

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

collapse all

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

collapse all

SQLite database connection, specified as an sqlite object created using the sqlite function.

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

Example: 'employees'

Data Types: char | string

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

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.

Compatibility Considerations

expand all

Not recommended starting in R2018a

See Also

| | | |

Topics

External Websites

Introduced before R2006a

Database Toolbox Documentation

Support