sqlwrite
Insert MATLAB data into database table
Description
sqlwrite(
inserts data from a MATLAB® table into a database table. 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 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','toy_store'
inserts data into a database table
that is located in the database catalog named toy_store
.
Examples
Append Data into Existing Table
Use an ODBC connection to append product data from a MATLAB® table into an existing table in a Microsoft® SQL Server® database.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable
.
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 = []
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 _____________ ___________ ______________ ________ __________________ 13 4.7082e+05 1012 17 'Pancakes' 14 5.101e+05 1011 19 'Shawl' 15 8.9975e+05 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)
ans = 4×5 table productNumber stockNumber supplierNumber unitCost productDescription _____________ ___________ ______________ ________ __________________ 13 4.7082e+05 1012 17 'Pancakes' 14 5.101e+05 1011 19 'Shawl' 15 8.9975e+05 1011 20 'Snacks' 30 5e+05 1000 25 'Rubik's Cube'
Close the database connection.
close(conn)
Insert Data into New Table
Use an ODBC connection to insert product data from MATLAB® into a new table in a Microsoft® SQL Server® database.
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 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 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 an ODBC connection to insert product data from MATLAB® into a new table in a Microsoft® SQL Server® database. Specify the data types of the columns in 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 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 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)
Insert Cell Array 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 cell array 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 cell array that contains data for two products.
c = {30,500000,1000,25,"Rubik's Cube";40,600000,2000,30,"Doll House"};
Convert the cell array to a MATLAB table by specifying the column names.
colnames = {'productNumber' 'stockNumber' 'supplierNumber' 'unitCost' ... 'productDescription'}; data = cell2table(c,'VariableNames',colnames);
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)
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)
Insert Numeric Array into Table
Use an ODBC connection to insert sales volume data from MATLAB® into an existing table in a Microsoft® SQL Server® database. Insert data stored as a numeric array into the existing database table.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the salesVolume
table.
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 numeric array that contains monthly sales volume data for a specific stock number. Specify the column names for the existing database table salesVolume
.
n = [100000 1000 0 2000 500 3000 450 600 700 750 1450 0 0]; colnames = {'StockNumber' 'January' 'February' 'March' 'April' 'May' ... 'June' 'July' 'August' 'September' 'October' 'November' 'December'};
Convert the numeric array to a MATLAB table.
data = array2table(n,'VariableNames',colnames);
Insert the sales volume data into the database table salesVolume
.
tablename = 'salesVolume';
sqlwrite(conn,tablename,data)
Import the contents of the database table into MATLAB and display the last three rows. The results contain a new row for the inserted sales volume data.
rows = sqlread(conn,tablename); tail(rows,3)
ans = 3×13 table StockNumber January February March April May June July August September October November December ___________ _______ ________ _____ _____ ____ ____ ____ ______ _________ _______ ________ ________ 5.101e+05 235 1800 1040 900 750 700 400 350 500 100 3000 18000 8.9975e+05 123 1700 823 701 689 621 545 421 495 650 4200 11000 1e+05 1000 0 2000 500 3000 450 600 700 750 1450 0 0
Close the database connection.
close(conn)
Insert Date Number into Table
Use an ODBC connection to insert inventory data from MATLAB® into an existing table in a Microsoft® SQL Server® database. Insert a date stored as a date number into the existing database table.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table inventoryTable
.
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 numeric array that contains inventory data for a specific product, including the date number 731011
. Specify the column names for the existing database table inventoryTable
.
n = [25 1000 50 731011]; colnames = {'productNumber' 'Quantity' 'Price' 'inventoryDate'};
Convert the numeric array to a MATLAB table.
data = array2table(n,'VariableNames',colnames);
Convert the date value in the inventory data to a datetime
array. The sqlwrite
function does not accept date numbers as a valid data type for insertion.
n = data.inventoryDate; data.inventoryDate = datetime(n,'ConvertFrom','datenum');
Import the contents of the database table inventoryTable
into MATLAB and display the last few rows.
tablename = 'inventoryTable';
rows = sqlread(conn,tablename);
tail(rows,3)
ans = 3×4 table productNumber Quantity Price inventoryDate _____________ ________ _____ _________________________ 11 567 11 '2012-09-11 00:30:24.000' 12 1278 22 '2010-10-29 18:17:47.000' 13 1700 17 '2009-05-24 10:58:59.000'
Insert the inventory data into the database table inventoryTable
. Specify the schema where the table is stored by using the 'Schema'
name-value pair argument.
sqlwrite(conn,tablename,data,'Schema','dbo')
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 inventory data.
rows = sqlread(conn,tablename); tail(rows,4)
ans = 4×4 table productNumber Quantity Price inventoryDate _____________ ________ _____ _________________________ 11 567 11 '2012-09-11 00:30:24.000' 12 1278 22 '2010-10-29 18:17:47.000' 13 1700 17 '2009-05-24 10:58:59.000' 25 1000 50 '2001-06-09 00:00:00.000'
Close the database connection.
close(conn)
Insert NULL
Number into Table
Use an ODBC connection to insert sales volume data from MATLAB® into an existing table in a Microsoft® SQL Server® database. Insert NULL
numbers into the existing database table.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table salesVolume
.
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 numeric array that contains monthly sales volume data for a specific stock number, and includes a NULL
number. The value Inf
indicates a NULL
value. Specify the column names for the existing database table salesVolume
.
n = [100000 Inf 0 2000 500 3000 450 600 700 750 1450 0 0]; colnames = {'StockNumber' 'January' 'February' 'March' 'April' 'May' ... 'June' 'July' 'August' 'September' 'October' 'November' 'December'};
Convert the numeric array to a MATLAB table.
data = array2table(n,'VariableNames',colnames);
Convert the Inf
value in the January
variable to NaN
. The sqlwrite
function does not accept Inf
values as valid missing data for insertion.
data.January = NaN;
Import the contents of the database table salesVolume
into MATLAB and display the last few rows.
tablename = 'salesVolume';
rows = sqlread(conn,tablename);
tail(rows,3)
ans = 3×13 table StockNumber January February March April May June July August September October November December ___________ _______ ________ _____ _____ ____ ____ ____ ______ _________ _______ ________ ________ 4.7082e+05 3100 9400 1540 1500 1350 1190 900 867 923 1400 3000 35000 5.101e+05 235 1800 1040 900 750 700 400 350 500 100 3000 18000 8.9975e+05 123 1700 823 701 689 621 545 421 495 650 4200 11000
Insert the sales volume data into the database table salesVolume
.
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 sales volume data.
rows = sqlread(conn,tablename); tail(rows,4)
ans = 4×13 table StockNumber January February March April May June July August September October November December ___________ _______ ________ _____ _____ ____ ____ ____ ______ _________ _______ ________ ________ 4.7082e+05 3100 9400 1540 1500 1350 1190 900 867 923 1400 3000 35000 5.101e+05 235 1800 1040 900 750 700 400 350 500 100 3000 18000 8.9975e+05 123 1700 823 701 689 621 545 421 495 650 4200 11000 1e+05 NaN 0 2000 500 3000 450 600 700 750 1450 0 0
Close the database connection.
close(conn)
Insert NULL
String into Table
Use an ODBC connection to insert product data from MATLAB® into an existing table in a Microsoft® SQL Server® database. Insert a NULL
string into the existing database table.
Create an ODBC database connection to a Microsoft SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table productTable
.
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 MATLAB table that contains data for one product and includes a NULL
value in the productDescription
variable.
data = table([30],[500000],[1000],[25], ... ["null"],'VariableNames',{'productNumber' ... 'stockNumber' 'supplierNumber' 'unitCost' 'productDescription'});
Convert the null
value in the productDescription
variable to ""
. The sqlwrite
function does not accept null
values as valid missing data for insertion.
data.productDescription(1) = "";
Import the contents of the existing database table productTable
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 _____________ ___________ ______________ ________ __________________ 13 4.7082e+05 1012 17 'Pancakes' 14 5.101e+05 1011 19 'Shawl' 15 8.9975e+05 1011 20 'Snacks'
Insert 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 _____________ ___________ ______________ ________ __________________ 13 4.7082e+05 1012 17 'Pancakes' 14 5.101e+05 1011 19 'Shawl' 15 8.9975e+05 1011 20 'Snacks' 30 5e+05 1000 25 ''
Close the database connection.
close(conn)
Insert Partial Data into Table
Use an ODBC connection to insert two columns of inventory data from MATLAB® into an existing table with a few columns in a Microsoft® SQL Server® database. After the insertion of data, the database table contains the data in the first two columns. The other columns have blank entries for the inserted rows of data.
Create an ODBC connection to a SQL Server database with Windows® authentication. Specify a blank user name and password. The database contains the table inventoryTable
with inventory data.
datasource = "MS SQL Server Auth"; conn = database(datasource,"","");
Create a table with two columns of data for the product number and quantity. This data represents two products to insert.
data = table([14;15],[350;400],'VariableNames',["productNumber" "Quantity"]);
Insert the inventory data into the database table inventoryTable
. This table contains four columns: product number, quantity, price, and inventory date. In this case, the product number and quantity for each product to insert are known but the price and inventory date are unknown.
tablename = "inventoryTable";
sqlwrite(conn,tablename,data)
Import the contents of the database table into MATLAB and display the last few rows. The results contain two new rows for the inserted inventory data. The sqlwrite
function inserts blank entries for the last two columns.
rows = sqlread(conn,tablename); tail(rows,3)
ans=3×4 table
productNumber Quantity Price inventoryDate
_____________ ________ _____ _______________________
13 1700 14.5 {'2009-05-24 10:58:59'}
14 350 NaN {0×0 char }
15 400 NaN {0×0 char }
Close the database connection.
close(conn)
Input Arguments
conn
— Database connection
connection
object
Database connection, specified as an ODBC connection
object or JDBC connection
object created using the
database
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
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.
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
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 | NUMERIC |
Duration array | TIME |
| DATE , TIME , or
DATETIME |
Logical array or cell array of logical arrays | BIT or
BOOLEAN |
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
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 | NUMERIC |
Datetime array | TIMESTAMP |
Duration array | TIME |
Logical array | NUMERIC |
String array |
Note The size of this column equals the sum of the maximum length of a string in the string array and 100.
|
Cell array of character vectors |
Note The size of this column equals the sum of the maximum length of a character vector in the cell 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 images, specify
'ColumnType',"image"
.
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 , Time , or
Timestamp | NaT |
duration array | Time | NaN |
double or
single array |
| NaN |
cell array of double or
single arrays |
| 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 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 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 R2018a
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)