sqlwrite

Insert MATLAB data into database table

Syntax

sqlwrite(conn,tablename,data)

sqlwrite(conn,tablename,data,Name=Value)

Description

sqlwrite(conn,tablename,data) inserts data from a MATLAB^® table into a database table.

• If the specified table already exists in the database, the function appends the rows from the MATLAB table to the existing table.

• If the table does not exist, the function creates a new table with the specified name and then inserts the data as rows.

This syntax is equivalent to executing SQL statements that use the CREATE TABLE and INSERT INTO commands defined by ANSI SQL.

conn can be any of the following connection objects:

• MySQL^®

• PostgreSQL^®

• DuckDB™

• SQLite

• ODBC

• JDBC

sqlwrite(conn,tablename,data,Name=Value) uses additional options specified by one or more name-value pair arguments. For example, Catalog="toy_store" inserts the data into a database table located in the database catalog named toy_store.

Examples

Append Data into Existing Table Using MySQL Native Interface

Use a MySQL® native interface database connection to append product data from a MATLAB® table into an existing table in a MySQL database.

Create a MySQL native interface database connection to a MySQL database using the data source name, user name, and password. The database contains the table productTable.

datasource = "MySQLNative";
username = "root";
password = "matlab";
conn = mysql(datasource,username,password);

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)

ans=3×5 table
     6    400876    1004     8     "Sail Boat"
     3    400999    1009    17        "Slinky"
    10    888652    1006    24    "Teddy Bear"

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)

ans=4×5 table
     6    400876    1004     8       "Sail Boat"
     3    400999    1009    17          "Slinky"
    10    888652    1006    24      "Teddy Bear"
    30    500000    1000    25    "Rubik's Cube"

Close the database connection.

close(conn)

Insert Data into New Table

Use a PostgreSQL native interface database connection to insert product data from MATLAB® into a new table in a PostgreSQL database.

Create a PostgreSQL native interface database connection to a PostgreSQL database using the data source name, user name, and password.

datasource = "PostgreSQLDataSource";
username = "dbdev";
password = "matlab";
conn = postgresql(datasource,username,password);

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
    30    500000    1000    25    "Rubik's Cube"
    40    600000    2000    30      "Doll House"

Close the database connection.

close(conn)

Insert Data into DuckDB Database Table

Create a transient, in-memory DuckDB™ database connection by using the duckdb function.

conn = duckdb();

Create a table to store product data that includes ID, name, and quantity information.

idData = [1001;1002;1003];
nameData = ["item1";"item2";"item3"];
quantityData = [250;100;400];
varNames = ["ID","Name","Quantity"];
productData = table(idData,nameData,quantityData,VariableNames=varNames)

productData=3×3 table
     ID      Name      Quantity
    ____    _______    ________

    1001    "item1"      250   
    1002    "item2"      100   
    1003    "item3"      400   

Export the table to the DuckDB™ database by using the sqlwrite function.

sqlwrite(conn,"productTable",productData)

Import the table from the DuckDB database into the MATLAB® workspace by using the sqlread function. Confirm that the values in data match the values in productData.

data = sqlread(conn,"productTable")

data=3×3 table
     ID      Name      Quantity
    ____    _______    ________

    1001    "item1"      250   
    1002    "item2"      100   
    1003    "item3"      400   

Close the database connection.

close(conn);

Specify Column Types When Inserting Data into New Table

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 an SQLite connection conn to the existing SQLite database file sample_dataset.db. The SQLite connection is an sqlite object.

dbfile = fullfile(matlabroot,"toolbox/database/database/sample_dataset.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 output contains 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)

Insert Structure into Table

Use an ODBC connection to insert product data from MATLAB® into a new table in a Microsoft® SQL Server® database. Insert data stored as a structure into the new database table.

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, then the connection is successful.

conn.Message

ans =

     []

Create a structure array that contains data for two products.

s(1).productNumber = 30;
s(1).stockNumber = 500000;
s(1).supplierNumber = 1000;
s(1).unitCost = 25;
s(1).productDescription = "Rubik's Cube";

s(2).productNumber = 40;
s(2).stockNumber = 600000;
s(2).supplierNumber = 2000;
s(2).unitCost = 30;
s(2).productDescription = "Doll House";

Convert the structure to a MATLAB table.

data = struct2table(s);

Insert the product data into a new database table 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'    

Close the database connection.

close(conn)

Input Arguments

conn — Database connection

MySQLconnection object | PostgreSQL connection object | DuckDB connection object | SQLite connection object | ODBC connection object | JDBC connection object

Database connection, specified as any of the following:

• MySQL connection object created by using the mysql function.

• PostgreSQL connection object created by using the postgresql function.

• DuckDB connection object created by using the duckdb function.

• SQLite connection object created by using the sqlite function.

• ODBC connection object created by using the database function.

• JDBC connection object created by using the database function.

tablename — Database table name

string scalar | character vector

Database table name, specified as a string scalar or character vector.

Example: "employees"

Data Types: string | char

data — Data to insert

table

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

MATLAB tables can include the following data types:

• Numeric arrays

• Cell array of numeric arrays

• Cell arrays of character vectors

• String arrays

• Datetime arrays

• Duration arrays

• Logical arrays

• Cell arrays of logical arrays

Numeric arrays can contain these data types: int8, uint8, int16, uint16, int32, uint32, int64, uint64, single, or double.

Supported date and time formats are:

• Date — 'yyyy-MM-dd'

• Time — 'hh:mm:ss'

• Timestamp — 'yyyy-MM-dd HH:mm:ss'

If date and time values are in an invalid format, sqlwrite automatically converts them to a supported format.

For string arrays or cell arrays of character vectors, the database driver validates the format. If the format is unexpected, the driver throws an error.

You can insert data into an existing database table or create a new one. The variable data types in data vary depending on whether the database table already exists.

Note

The sqlwrite function supports only the table data type for data. To insert data stored in a structure, cell array, or numeric matrix, convert it to a table using:

• struct2table

• cell2table

• array2table

The sqlwrite function does not support the database preferences NullNumberWrite and NullStringWrite. To insert missing data, see Accepted Missing Data.

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

Data Types for Existing Table

When inserting MATLAB table data into an existing database table, the variable names in the MATLAB table must match the column names in the database table, including case.

Use the data types in the mapping table below to ensure compatibility between MATLAB variables and database columns. For example, if the database column uses the BOOLEAN data type, the corresponding MATLAB variable should be a LOGICAL array or a cell array of logical arrays.

MATLAB DataType	MySQL	PostgreSQL	DuckDB	SQLite	JDBC/ODBC
numeric array or cell array of numeric arrays	INTEGER SMALLINT DECIMAL NUMERIC FLOAT REAL DOUBLE PRECISION	SMALLINT INTEGER BIGINT DECIMAL NUMERIC REAL DOUBLE PRECISION SMALLSERIAL SERIAL BIGSERIAL	TINYINT SMALLINT INTEGER BIGINT HUGEINT UTINYINT USMALLINT UINTEGER UBIGINT UHUGEINT FLOAT DOUBLE DECIMAL	DOUBLE	NUMERIC
cell array of character vectors, string array, datetime array, or duration array	DATE TIME TIMESTAMP	DATE TIME TIMESTAMP	DATE TIME TIMESTAMP_WITH_TIMEZONE	N/A	TIME
logical array or cell array of logical arrays	BIT	BIT	BOOLEAN	DOUBLE	BIT or BOOLEAN
cell array of character vectors or string array	CHAR VARCHAR	CHAR VARCHAR	VARCHAR	VARCHAR	CHAR VARCHAR TEXT NTEXT Other text data type
calendarDuration array	N/A	INTERVAL	INTERVAL	N/A	N/A

Data Types for New Table

When inserting MATLAB table data into a new database table, sqlwrite:

• Requires unique table names.

• Ignores variables with unsupported data types and only inserts valid variables as columns.

• Maps each MATLAB variable to a default database column type.

The table below shows supported MATLAB data types and their corresponding default database types.

MATLAB Data Type	MySQL	PostgreSQL	DuckDB	SQLite	JDBC/ODBC
int8 array	TINYINT	SMALLINT	TINYINT	N/A	N/A
int16 array	SMALLINT	SMALLINT	SMALLINT	N/A	N/A
int32 array	INTEGER	INTEGER	INTEGER	N/A	N/A
int64 array	BIGINT	BIGINT	BIGINT	N/A	N/A
logical array	BIT	BOOLEAN	BOOLEAN	DOUBLE	NUMERIC
single or double array	NUMERIC	NUMERIC	FLOAT or DOUBLE	N/A	N/A
datetime array	TIMESTAMP	TIMESTAMP	TIMESTAMP	N/A	TIMESTAMP
duration array	TIME	TIME	TIME	N/A	TIME
cell array of character vectors or string array	VARCHAR *	VARCHAR *	VARCHAR	N/A	N/A
calendarDuration array	N/A	INTERVAL	INTERVAL	N/A	N/A
numeric array or cell array of numeric arrays	N/A	N/A	DOUBLE	DOUBLE	NUMERIC
string array, cell array of character vectors, or datetime arrays	N/A	N/A	N/A	VARCHAR	N/A
string array	N/A	N/A	N/A	N/A	VARCHAR
cell array of character vectors	N/A	N/A	N/A	N/A	VARCHAR *
* Column size is set to the maximum string length found in the array plus 100 characters.

To use database-specific column data types instead of defaults, specify the ColumnType name-value argument. For example, set ColumnType="BIGINT" to create a column with the BIGINT data type in the new table. ColumnType also allows you to specify other data types, such as "image" or "money", that are not in the above table.

Accepted Missing Data

The accepted missing data values depend on the MATLAB table variable and the database column. The table below maps each MATLAB data type to its database column type and lists the accepted missing data for each case.

Data Type of MATLAB Table Variable	Data Type of Database Column	Accepted Missing Data
datetime array	DATE, TIME, or TIMESTAMP	NaT
duration array	TIME	NaN
double or single array	NUMERIC DOUBLE FLOAT DECIMAL REAL	NaN
cell array of double or single arrays	NUMERIC DOUBLE FLOAT DECIMAL REAL	NaN, [], or ''
cell array of character vectors	DATE, TIME, or TIMESTAMP	'NaT' or ''
cell array of character vectors	CHAR, VARCHAR, or other text data type	''
string array	DATE, TIME, or TIMESTAMP	"", "NaT", or missing
string array	CHAR, VARCHAR, or other text data type	missing

Data Types: table

Name-Value Arguments

Specify optional pairs of arguments as Name1=Value1,...,NameN=ValueN, where Name is the argument name and Value is the corresponding value. Name-value arguments must appear after other arguments, but the order of the pairs does not matter.

Before R2021a, use commas to separate each name and value, and enclose Name in quotes.

Example: sqlwrite(conn,"tablename",data,ColumnType=["numeric" "timestamp" "image"]) inserts data into a new database table named tablename by specifying data types for columns in the new database table.

Catalog — Database catalog name

string scalar | character vector

Database catalog name, specified as a string scalar or character vector. A catalog serves as the container for the schemas in a database and contains related metadata information. A database can have multiple catalogs.

Example: Catalog = "toy_store"

Data Types: string | char

Schema — Database schema name

string scalar | character vector

Database schema name, specified as a string scalar or character vector. A schema defines the database tables, views, relationships among tables, and other elements. A database catalog can have numerous schemas.

Example: Schema = "dbo"

Data Types: string | char

ColumnType — Database column type

string scalar | string array | character vector | cell array of character vectors

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

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

Data Types: string | char | cell

checkTableExists — Table existence

true (default) | false

Table existence, specified as true or false.

• true — sqlwrite checks for table existence and automatically creates one if it does not exist.

• false — sqlwrite does not check for table existence.

Example: sqlwrite(conn,tableName,table(data,'VariableNames',{'COL1'}),checkTableExists=false);

Version History

Introduced in R2018a

R2026a: sqlwrite supports DuckDB connection objects

You can now insert MATLAB data into a DuckDB database by specifying a DuckDB connection object when using the sqlwrite function.

R2025a: Control automatic table creation in sqlwrite

Use the name-value argument, checkTableExists, to check the existence of a table and control automatic table creation when you export data with sqlwrite.