Main Content

sqlwrite

Insert MATLAB data into database table

collapse all in page

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 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,Name,Value) uses additional options specified by one or more name-value pair arguments. For example, 'Catalog','toy_store' inserts data into a database table that is located in the database catalog named toy_store.

example

Examples

collapse all

Open Script

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)
Open Script

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)
Open Script

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)
Open Script

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)
Open Script

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)
Open Script

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)
Open Script

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)
Open Script

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)
Open Script

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)
Open Live Script

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

collapse all

Database connection, specified as an ODBC connection object or JDBC connection object created using the database function.

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 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 VariableData Type of Existing Database Column
Numeric array or cell array of numeric arraysNUMERIC

Duration array

TIME

  • Cell array of character vectors

  • String array

  • Datetime array

DATE, TIME, or DATETIME
Logical array or cell array of logical arraysBIT or BOOLEAN
Cell array of character vectors or string array

  • CHAR

  • VARCHAR

  • TEXT

  • NTEXT

  • Other text data type

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 VariableDefault Data Type of Database Column
Numeric array or cell array of numeric arraysNUMERIC

Datetime array

TIMESTAMP

Duration array

TIME
Logical arrayNUMERIC
String array

VARCHAR

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

VARCHAR

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 VariableData Type of Database ColumnAccepted Missing Data
datetime arrayDate, Time, or TimestampNaT
duration arrayTimeNaN
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 vectorsDate, Time, or Timestamp'NaT' or ''
cell array of character vectorsChar, Varchar, or other text data type''
string arrayDate, Time, or Timestamp"", "NaT", or missing
string arrayChar, Varchar, or other text data typemissing

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.

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

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

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

See Also

| | | | |

Topics