Documentation

This is machine translation

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

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

insert

Add MATLAB data to database tables

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)

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

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

This database contains the table yearlySales with these columns: Month, salesTotal, and Revenue.

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

curs = exec(conn,'SELECT * FROM yearlySales');
curs = fetch(curs);
curs.Data
ans = 

      Month      salesTotal    Revenue
    ---------    ----------    -------
    'January'    130           1200   
    'Feb'         25            250   

Store the column names of yearlySales in a cell array.

colnames = {'Month','salesTotal','Revenue'};

Store the data for insertion in a cell array. The data contains Month equal to 'March', salesTotal equal to $50, and Revenue equal to $2000.

data = {'March',50,2000};

Insert the data into yearlySales.

tablename = 'yearlySales';
insert(conn,tablename,colnames,data)

Display the data from yearlySales again.

curs = exec(conn,'SELECT * FROM yearlySales');
curs = fetch(curs);
curs.Data
ans = 

      Month      salesTotal    Revenue
    ---------    ----------    -------
    'January'    130           1200   
    'Feb'         25            250   
    'March'       50           2000   

A new row appears in yearlySales with the data from data.

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

close(curs)

Close the database connection.

close(conn)

Create a table in a new SQLite database file and insert a new row of data into the table.

Create a SQLite connection conn to a new SQLite database file tutorial.db. Specify the file name in the current working folder.

dbfile = fullfile(pwd,'tutorial.db');

conn = sqlite(dbfile,'create');

Create the table inventoryTable using exec.

createInventoryTable = ['create table inventoryTable ' ...
    '(productNumber NUMERIC, Quantity NUMERIC, ' ...
    'Price NUMERIC, inventoryDate VARCHAR)'];

exec(conn,createInventoryTable)

inventoryTable is an empty table in tutorial.db.

Insert a row of data into inventoryTable.

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

insert(conn,'inventoryTable',colnames, ...
    {20,150,50.00,'11/3/2015 2:24:33 AM'})

Close the SQLite connection.

close(conn)

Input Arguments

collapse all

Database connection, specified as a connection object or sqlite object created using the database or sqlite functions.

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

Data Types: char | string

Database table column names, specified as a cell array of one or more character vectors or 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.

For the MATLAB interface to SQLite, use only insert. For details, see Working with MATLAB Interface to SQLite.

For other differences among these functions, see Inserting Data Using Command Line.

Introduced before R2006a

Was this topic helpful?