sqlwrite
Description
sqlwrite(
inserts data from a MATLAB® table into a database table. If the table exists in the database, this
function appends the data from 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 conn
,tablename
,data
)CREATE TABLE
and INSERT INTO
ANSI SQL
syntaxes.
sqlwrite(
uses additional options specified by one or more name-value pair arguments. For example,
conn
,tablename
,data
,Name,Value
)'Catalog',"toystore_doc"
inserts data into a database table that is
located in the database catalog named toystore_doc
.
Examples
Append Data into Existing Table Using PostgreSQL Native Interface
Use a PostgreSQL native interface database connection to append product data from a MATLAB® table into an existing 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. The database contains the table productTable
.
datasource = "PostgreSQLDataSource"; username = "dbdev"; password = "matlab"; conn = postgresql(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
productnumber stocknumber suppliernumber unitcost productdescription
_____________ ___________ ______________ ________ __________________
6 4.0088e+05 1004 8 "Sail Boat"
3 4.01e+05 1009 17 "Slinky"
10 8.8865e+05 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
productnumber stocknumber suppliernumber unitcost productdescription
_____________ ___________ ______________ ________ __________________
6 4.0088e+05 1004 8 "Sail Boat"
3 4.01e+05 1009 17 "Slinky"
10 8.8865e+05 1006 24 "Teddy Bear"
30 5e+05 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
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)
Specify Column Types When Inserting 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. Specify the data types of the columns in the new database table.
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
. Use the 'ColumnType
' name-value pair 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 5e+05 1000 25 "Rubik's Cube"
40 6e+05 2000 30 "Doll House"
Close the database connection.
close(conn)
Input Arguments
conn
— PostgreSQL native interface database connection
connection
object
PostgreSQL native interface database connection, specified as a connection
object.
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
Duration array
Calendar duration 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.
To insert missing data, see Accepted Missing Data.
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 BIT
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 |
|
Cell array of character vectors, string array, datetime array, or duration array |
|
Calendar duration array | interval |
Logical array or cell array of logical arrays | bit |
Cell array of character vectors or string array |
|
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
Duration array
Calendar duration array
Logical array
The sqlwrite
function ignores any invalid data 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 |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
cell array of character vectors or string array |
Note The size of this column equals the sum of the maximum length of a string in the string array and 100. |
To specify database-specific column data types instead of the defaults, use the
'ColumnType'
name-value pair argument. For example, you can
specify 'ColumnType',"bigint"
to create a bigint
column in the new database table.
Also, using the 'ColumnType'
name-value pair argument, you
can specify other data types that are not in the default list. For example, to insert
currency, specify 'ColumnType',"money"
.
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 | Data Type of Database Column | Accepted Missing Data |
---|---|---|
datetime array | date or timestamp | NaT |
duration array | time | NaN |
calendarDuration array | interval | NaN |
double or single array or cell
array of double or single arrays |
| NaN , [] , or
'' |
cell array of character vectors | date or timestamp | 'NaT' or '' |
cell array of character vectors | time | 'NaN' or '' |
cell array of character vectors | char , varchar , or other text data
type | '' |
string array | date or timestamp | "" , "NaT" , or
missing |
string array | time | "" , "NaN" , 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"
"money"])
inserts data into a new database table named
tablename
by specifying data types for all 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 types
character vector | string scalar | cell array of character vectors | string array
Database column types, specified as the comma-separated pair consisting of
'ColumnType'
and a character vector, string scalar, cell array of
character vectors, or string array. Use this name-value pair argument to define custom
data types for the columns in a database table. Specify a column type for each column
in the table.
Example: 'ColumnType',["numeric" "varchar(400)"]
Data Types: char
| string
| cell
Version History
Introduced in R2020b
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)