sqlwrite

Insert MATLAB data into SQLite database table

Since R2022a

Syntax

sqlwrite(conn,tablename,data)

sqlwrite(conn,tablename,data,ColumnType=columntypes)

Description

sqlwrite(conn,tablename,data) inserts data from a MATLAB^® table into a database table with the MATLAB interface to SQLite. If the table exists in the database, this function appends the data in the MATLAB table as rows in the existing database table. If the table does not exist in the database, this function creates a table with the specified table name and then inserts the data as rows in the new table. This syntax is the equivalent of executing SQL statements that contain the CREATE TABLE and INSERT INTO ANSI SQL syntaxes.

example

sqlwrite(conn,tablename,data,ColumnType=columntypes) specifies the data type for the column in the SQLite database.

Examples

collapse all

Append Data into Existing Table

Open Live Script

Use the MATLAB® interface to SQLite to append product data from a MATLAB® table into an existing table in an SQLite database.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

To view the existing database table productTable before appending data, import its contents into MATLAB and display the last few rows.

tablename = "productTable";
rows = sqlread(conn,tablename);
tail(rows,3)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         13            470816            1012            16           "Pancakes"    
         14            510099            1011            19           "Shawl"       
         15            899752            1011            20           "Snacks"

Create a MATLAB table that contains the data for one product.

data = table(30,500000,1000,25,"Rubik's Cube", ...
    'VariableNames',["productNumber" "stockNumber" ...
    "supplierNumber" "unitCost" "productDescription"]);

Append the product data into the database table productTable.

sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB again and display the last few rows. The results contain a new row for the inserted product.

rows = sqlread(conn,tablename);
tail(rows,4)

    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         13            470816            1012            16         "Pancakes"      
         14            510099            1011            19         "Shawl"         
         15            899752            1011            20         "Snacks"        
         30            500000            1000            25         "Rubik's Cube"

Delete the inserted row to maintain the dataset.

sqlquery = "DELETE FROM productTable WHERE productnumber = 30";
execute(conn,sqlquery)

Close the database connection.

close(conn)

Insert Data into New Table

Open Live Script

Use the MATLAB® interface to SQLite to insert product data from MATLAB into a new table in an SQLite database.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

Create a MATLAB table that contains data for two products.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

Insert the product data into a new database table named toyTable.

tablename = "toyTable";
sqlwrite(conn,tablename,data)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)

rows=2×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         30             5e+05            1000            25         "Rubik's Cube"  
         40             6e+05            2000            30         "Doll House"

Delete the new table to maintain the dataset.

sqlquery = "DROP TABLE toyTable";
execute(conn,sqlquery)

Close the database connection.

close(conn)

Specify Column Types When Inserting Data into New Table

Open Live Script

Use the MATLAB® interface to SQLite to insert product data from MATLAB into a new table in an SQLite database. Specify the data types of the columns in the new database table.

Create the SQLite connection conn to the existing SQLite database file tutorial.db. The database file contains the table productTable. The SQLite connection is an sqlite object.

dbfile = "tutorial.db";
conn = sqlite(dbfile);

Create a MATLAB table that contains data for two products.

data = table([30;40],[500000;600000],[1000;2000],[25;30], ...
    ["Rubik's Cube";"Doll House"],'VariableNames',["productNumber" ...
    "stockNumber" "supplierNumber" "unitCost" "productDescription"]);

Insert the product data into a new database table named toyTable. Use the ColumnType name-value argument and a string array to specify the data types of all the columns in the database table.

tablename = "toyTable";
coltypes = ["numeric" "numeric" "numeric" "numeric" "varchar(255)"];
sqlwrite(conn,tablename,data,ColumnType=coltypes)

Import the contents of the database table into MATLAB and display the rows. The results contain two rows for the inserted products.

rows = sqlread(conn,tablename)

rows=2×5 table
    productNumber    stockNumber    supplierNumber    unitCost    productDescription
    _____________    ___________    ______________    ________    __________________

         30            500000            1000            25         "Rubik's Cube"  
         40            600000            2000            30         "Doll House"

Delete the new table to maintain the dataset.

sqlquery = "DROP TABLE toyTable";
execute(conn,sqlquery)

Close the database connection.

close(conn)

Input Arguments

collapse all

`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

`data` — Data to insert
table

Data to insert into a database table, specified as a table.

The valid data types in a MATLAB table are:

Numeric array
Cell array of numeric arrays
Cell array of character vectors
String array
Datetime array
Logical array
Cell array of logical arrays

The numeric array can contain these data types:

int8
uint8
int16
uint16
int32
uint32
int64
uint64
single
double

For date and time data, supported formats are:

Date — 'yyyy-MM-dd'
Time — 'hh:mm:ss'
Timestamp — 'yyyy-MM-dd HH:mm:ss'

If the date and time data is specified in an invalid format, then the sqlwrite function automatically converts the data to a supported format.

If the cell array of character vectors or string array is specified in an invalid format, then the sqlwrite function enables the database driver to check the format. If the format is unexpected, then the database driver throws an error.

You can insert data in an existing database table or a new database table. The data types of variables in data vary depending on whether the database table exists. For valid data types, see Data Types for Existing Table and Data Types for New Table.

Note

The sqlwrite function supports only the table data type for the data input argument. To insert data stored in a structure, cell array, or numeric matrix, convert the data to a table by using the struct2table, cell2table, and array2table functions, respectively.

Example: table([10;20],{'M';'F'})

Data Types for Existing Table

The variable names of the MATLAB table must match the column names in the database table. The sqlwrite function is case-sensitive.

When you insert data into a database table, use the data types shown in the following table to ensure that the data has the correct data type. This table matches the valid data types of the MATLAB table variable to the data types of the database column. For example, when you insert data into a database column that has the BOOLEAN data type, ensure that the corresponding variable in the MATLAB table is a logical array or cell array of logical arrays.

Data Type of MATLAB Table Variable	Data Type of Existing Database Column
Numeric array or cell array of numeric arrays	`DOUBLE`
String array Cell array of character vectors Datetime array	`VARCHAR`
Logical array	`DOUBLE`

Data Types for New Table

The specified table name for the new database table must be unique across all tables in the database.

The valid data types in a MATLAB table are:

Numeric array
Cell array of character vectors
String array
Datetime array
Logical array

The sqlwrite function ignores any invalid variable types and inserts only the valid variables from MATLAB as columns in a new database table.

The sqlwrite function converts the data type of the variable into the default data type of the column in the database table. The following table matches the valid data types of the MATLAB table variable to the default data types of the database column.

Data Type of MATLAB Table Variable	Default Data Type of Database Column
Numeric array or cell array of numeric arrays	`DOUBLE`
String array Cell array of character vectors Datetime array	`VARCHAR`
Logical array	`DOUBLE`

To specify database-specific column data types instead of the defaults, use the ColumnType name-value argument. For example, you can specify ColumnType="DOUBLE" to create a DOUBLE column in the new database table.

Accepted Missing Data

The accepted missing data for inserting data into a database depends on the data type of the MATLAB table variable and the data type of the column in the database. The following table matches the data type of the MATLAB table variable to the data type of the database column and specifies the accepted missing data to use in each case.

Data Type of MATLAB Table Variable	Accepted Missing Data
Numeric array	`NaN`
String array	`missing`
Cell array of character vectors	`''`
Datetime array	`NaT`

Data Types: table

`columntypes` — Database column types
character vector | string scalar | cell array of character vectors | string array

Database column types, specified as a character vector, string scalar, cell array of character vectors, or string array. Use this argument to define custom data types for the columns in a database table. Specify a column type for each column in the table.

Example: ["numeric" "varchar(400)"]

Data Types: cell | char | string

Version History

Introduced in R2022a

sqlwrite

Syntax

Description

Examples

Append Data into Existing Table

Insert Data into New Table

Specify Column Types When Inserting Data into New Table

Input Arguments

`conn` — SQLite database connection
`sqlite` object

`tablename` — Database table name
string scalar | character vector

`data` — Data to insert
table

Data Types for Existing Table

Data Types for New Table

Accepted Missing Data

`columntypes` — Database column types
character vector | string scalar | cell array of character vectors | string array

Version History

See Also

Objects

Functions

Topics

sqlwrite

Syntax

Description

Examples

Append Data into Existing Table

Insert Data into New Table

Specify Column Types When Inserting Data into New Table

Input Arguments

conn — SQLite database connection sqlite object

tablename — Database table name string scalar | character vector

data — Data to insert table

Data Types for Existing Table

Data Types for New Table

Accepted Missing Data

columntypes — Database column types character vector | string scalar | cell array of character vectors | string array

Version History

See Also

Objects

Functions

Topics

`conn` — SQLite database connection
`sqlite` object

`tablename` — Database table name
string scalar | character vector

`data` — Data to insert
table

`columntypes` — Database column types
character vector | string scalar | cell array of character vectors | string array