(To be removed) Add MATLAB data to database tables
The fastinsert function will be removed in a future release. Use
the sqlwrite
function instead. For details, see Compatibility Considerations.
fastinsert(
exports data from the MATLAB® workspace and inserts it into an existing database table using the
database connection conn,tablename,colnames,data)conn. You can specify the database table name
and column names, and specify the data for insertion into the database.
You do not specify the type of data you are exporting. The data is exported in its current MATLAB format.
First, connect to the Microsoft® SQL Server® database. Then, export data from MATLAB® into the database and close the database connection.
Create a 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, the connection is successful.
conn.Message
ans =
[]
Select and display all rows in the table sorted by the product number using the select function.
selectquery = 'SELECT * FROM productTable ORDER BY productNumber';
data = select(conn,selectquery)
data =
productNumber stockNumber supplierNumber unitCost productDescription
_____________ ___________ ______________ ________ __________________
1 4.0035e+05 1001 14 'Building Blocks'
2 4.0031e+05 1002 9 'Painting Set'
3 4.01e+05 1009 17 'Slinky'
Store the column names of productTable in a cell array.
tablename = 'productTable'; colnames = {'productNumber','stockNumber','supplierNumber', ... 'unitCost','productDescription'};
Store the data for the insert in a cell array that contains these values:
productNumber equal to 4
stockNumber equal to 500565
supplierNumber equal to 1010
unitCost equal to $20
productDescription equal to 'Cooking Set'
Then, convert the cell array to a table.
insertdata = {4,500565,1010,20,'Cooking Set'};
insertdata = cell2table(insertdata,'VariableNames',colnames)
insertdata =
productNumber stockNumber supplierNumber unitCost productDescription
_____________ ___________ ______________ ________ __________________
4 5.0057e+05 1010 20 'Cooking Set'
Insert data into the table.
fastinsert(conn,tablename,colnames,insertdata)
Select and display all rows in the table again.
data = select(conn,selectquery)
data =
productNumber stockNumber supplierNumber unitCost productDescription
_____________ ___________ ______________ ________ __________________
1 4.0035e+05 1001 14 'Building Blocks'
2 4.0031e+05 1002 9 'Painting Set'
3 4.01e+05 1009 17 'Slinky'
4 5.0057e+05 1010 20 'Cooking Set'
A new row appears in the productTable with data from insertdata.
Close the database connection.
close(conn)
First, connect to the Microsoft® SQL Server® database. Then, export multiple rows of data from MATLAB® into the database and close the database connection.
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, the connection is successful.
conn.Message
ans =
[]
Select and display data in the table inventoryTable. Import data using the select function.
selectquery = 'SELECT * FROM inventoryTable';
data = select(conn,selectquery)
data =
productNumber Quantity Price inventoryDate
_____________ ________ _____ _____________
1 1700 15 '2014-09-23'
2 1200 9 '2014-07-08'
3 356 17 '2014-05-14'
4 2580 21 '2013-06-08'
5 9000 3 '2012-09-14'
6 4540 8 '2013-12-25'
7 6034 16 '2014-08-06'
8 8350 5 '2011-06-18'
9 2339 13 '2011-02-09'
10 723 24 '2012-03-14'
Assign multiple rows of data to the cell array insertdata. Each row contains data for the columns in inventoryTable. The first row of data contains:
Product number is 11
Quantity is 125
Price is $23.00
Inventory date is the current date
insertdata = {11,125,23.00,datestr(now,'yyyy-mm-dd'); ...
12,1160,14.7,datestr(now,'yyyy-mm-dd'); ...
13,150,54.5,datestr(now,'yyyy-mm-dd')};
Store the column names of inventoryTable in a cell array.
tablename = 'inventoryTable'; colnames = {'productNumber','Quantity','Price','inventoryDate'};
Insert data into the table.
fastinsert(conn,tablename,colnames,insertdata)
Select and display data in the table inventoryTable again.
data = select(conn,selectquery)
data =
productNumber Quantity Price inventoryDate
_____________ ________ _____ _____________
1 1700 15 '2014-09-23'
2 1200 9 '2014-07-08'
3 356 17 '2014-05-14'
4 2580 21 '2013-06-08'
5 9000 3 '2012-09-14'
6 4540 8 '2013-12-25'
7 6034 16 '2014-08-06'
8 8350 5 '2011-06-18'
9 2339 13 '2011-02-09'
10 723 24 '2012-03-14'
11 125 23 '2016-11-02'
12 1160 15 '2016-11-02'
13 150 55 '2016-11-02'
Three new rows appear in inventoryTable with data from insertdata.
Close the database connection.
close(conn)
First, connect to the Microsoft® SQL Server® database. Then, export numeric data from MATLAB® into the database and close the database connection.
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, the connection is successful.
conn.Message
ans =
[]
Define the numeric matrix numdata that contains sales volume data.
numdata = [777666,0,350,400,450,250,450,500,515,235,100,300,600];
Select and display data in the salesVolume table before insertion. Import data using the select function.
selectquery = 'SELECT * FROM salesVolume';
data = select(conn,selectquery)
data =
StockNumber January February March April May June July August September October November December
___________ _______ ________ _____ _____ ____ ____ ____ ______ _________ _______ ________ ________
1.2597e+05 1400 1100 981 882 794 752 654 773 809 980 3045 19000
2.1257e+05 2400 1721 1414 1191 983 825 731 653 723 790 1400 5000
3.8912e+05 1800 1200 890 670 550 450 400 410 402 450 1200 16000
4.0031e+05 3000 2400 1800 1500 1200 900 700 650 1670 2500 6000 15000
4.0034e+05 4300 0 2600 1800 1600 1550 895 700 750 900 8000 24000
4.0035e+05 5000 3500 2800 2300 1700 1400 1000 900 1600 3300 12000 20000
4.0046e+05 1200 900 800 500 399 345 300 175 760 1500 5500 17000
4.0088e+05 3000 2400 1500 1500 1300 1100 900 867 923 1100 4000 32000
4.01e+05 3000 1500 1000 900 750 700 400 350 500 1100 3000 12000
8.8865e+05 0 900 821 701 689 621 545 421 495 550 4200 12000
4.0814e+05 6000 3100 8800 2300 1700 1400 1000 900 1600 3300 12000 25000
2.1046e+05 1800 9700 800 500 3997 349 300 175 760 1500 5500 27000
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
Store the column names of salesVolume in a cell array.
tablename = 'salesVolume'; colnames = {'stockNumber','January','February','March','April','May', ... 'June','July','August','September','October','November', ... 'December'};
Insert data into the table.
fastinsert(conn,tablename,colnames,numdata)
Select and display data in the salesVolume table again.
data = select(conn,selectquery)
data =
StockNumber January February March April May June July August September October November December
___________ _______ ________ _____ _____ ____ ____ ____ ______ _________ _______ ________ ________
1.2597e+05 1400 1100 981 882 794 752 654 773 809 980 3045 19000
2.1257e+05 2400 1721 1414 1191 983 825 731 653 723 790 1400 5000
3.8912e+05 1800 1200 890 670 550 450 400 410 402 450 1200 16000
4.0031e+05 3000 2400 1800 1500 1200 900 700 650 1670 2500 6000 15000
4.0034e+05 4300 0 2600 1800 1600 1550 895 700 750 900 8000 24000
4.0035e+05 5000 3500 2800 2300 1700 1400 1000 900 1600 3300 12000 20000
4.0046e+05 1200 900 800 500 399 345 300 175 760 1500 5500 17000
4.0088e+05 3000 2400 1500 1500 1300 1100 900 867 923 1100 4000 32000
4.01e+05 3000 1500 1000 900 750 700 400 350 500 1100 3000 12000
8.8865e+05 0 900 821 701 689 621 545 421 495 550 4200 12000
4.0814e+05 6000 3100 8800 2300 1700 1400 1000 900 1600 3300 12000 25000
2.1046e+05 1800 9700 800 500 3997 349 300 175 760 1500 5500 27000
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
7.7767e+05 0 350 400 450 250 450 500 515 235 100 300 600
A new row appears in salesVolume with data from numdata.
Close the database connection.
close(conn)
First, connect to the Microsoft® SQL Server® database. Then, export data from MATLAB® into the database and commit the insert transaction. Close the database connection.
Create an ODBC database connection to a Microsoft® SQL Server® database with Windows® authentication. Specify a blank user name and password. Use the name-value pair argument AutoCommit to specify manually committing transactions to the database.
datasource = 'MS SQL Server Auth'; conn = database(datasource,'','','AutoCommit','off');
Check the database connection. If the Message property is empty, the connection is successful.
conn.Message
ans =
[]
Insert the cell array data into the table inventoryTable with column names colnames.
data = {157,358,740.00,datestr(now,'yyyy-mm-dd HH:MM:SS')};
colnames = {'productNumber','Quantity','Price','inventoryDate'};
tablename = 'inventoryTable';
fastinsert(conn,tablename,colnames,data)
Commit the insert transaction.
commit(conn)
Close the database connection.
close(conn)
First, connect to the Microsoft® SQL Server® database. Then, export Boolean data from MATLAB® into the database. Close the database connection.
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,'','');
This database contains the table Invoice with these columns:
InvoiceNumber
InvoiceDate
productNumber
Paid
Receipt
Check the database connection. If the Message property is empty, then the connection is successful.
conn.Message
ans =
[]
Display the data in the Invoice table before insertion.
selectquery = 'SELECT * FROM Invoice';
data = select(conn,selectquery)
data =
10×5 table
InvoiceNumber InvoiceDate ProductNumber Paid Receipt
_____________ _________________________ _____________ _____ ______________
2101 '2010-08-01 00:00:00.000' 1 false [8000×1 uint8]
3546 '2010-03-01 00:00:00.000' 2 true [8000×1 uint8]
33116 '2011-05-15 00:00:00.000' 3 true [8000×1 uint8]
34155 '2011-07-12 00:00:00.000' 4 false [8000×1 uint8]
34267 '2011-07-22 00:00:00.000' 5 true [8000×1 uint8]
37197 '2011-09-03 00:00:00.000' 6 true [8000×1 uint8]
37281 '2011-09-21 00:00:00.000' 7 false [8000×1 uint8]
41011 '2011-12-12 00:00:00.000' 8 true [8000×1 uint8]
61178 '2012-01-15 00:00:00.000' 9 false [8000×1 uint8]
62145 '2012-01-23 00:00:00.000' 10 true [8000×1 uint8]
Create the variable insertdata as a structure containing the invoice number 2105, product number 11, and the Boolean data false to signify unpaid. Boolean data is represented as the MATLAB® data type logical. This code assumes that the receipt image is missing.
insertdata.InvoiceNumber{1} = 2105;
insertdata.InvoiceDate{1} = datestr(now,'yyyy-mm-dd HH:MM:SS');
insertdata.productNumber{1} = 11;
insertdata.Paid{1} = false;
Insert the paid invoice data into the Invoice table with column names colnames using the database connection.
colnames = {'InvoiceNumber';'InvoiceDate';'productNumber';'Paid'};
tablename = 'Invoice';
fastinsert(conn,tablename,colnames,insertdata)
View the new record in the database to verify that the Paid column value is Boolean. In some databases, the MATLAB® logical value false shows as a Boolean false, No, or a cleared check box.
data = select(conn,selectquery)
data =
11×5 table
InvoiceNumber InvoiceDate ProductNumber Paid Receipt
_____________ _________________________ _____________ _____ ______________
2101 '2010-08-01 00:00:00.000' 1 false [8000×1 uint8]
3546 '2010-03-01 00:00:00.000' 2 true [8000×1 uint8]
33116 '2011-05-15 00:00:00.000' 3 true [8000×1 uint8]
34155 '2011-07-12 00:00:00.000' 4 false [8000×1 uint8]
34267 '2011-07-22 00:00:00.000' 5 true [8000×1 uint8]
37197 '2011-09-03 00:00:00.000' 6 true [8000×1 uint8]
37281 '2011-09-21 00:00:00.000' 7 false [8000×1 uint8]
41011 '2011-12-12 00:00:00.000' 8 true [8000×1 uint8]
61178 '2012-01-15 00:00:00.000' 9 false [8000×1 uint8]
62145 '2012-01-23 00:00:00.000' 10 true [8000×1 uint8]
2105 '2017-01-04 10:19:42.000' 11 false ''
The last row contains the Boolean data false.
Close the database connection.
close(conn)
conn — Database connectionconnection objectDatabase connection, specified as a connection object created with the
database function.
tablename — Database table nameDatabase table name, specified as a character vector or string scalar denoting the name of a table in the database.
Example: 'employees'
Data Types: char | string
colnames — Database table column namesDatabase table column names, specified as a cell array of one or more character vectors or a
string array to denote the columns in the existing database table
tablename.
Example: {'col1','col2','col3'}
Data Types: cell | string
data — Data to insertData to insert, specified as a numeric matrix, cell array, table, dataset
array, or structure that contains all data for insertion into the existing
database table tablename. If data is a
structure, then field names in the structure must match
colnames. If data is a table or a
dataset array, then the variable names in the table or dataset array must
match colnames.
To insert data into a structure, table, or dataset array, use this special
formatting. Each field or variable in a structure, table, or dataset array
must be a cell array or double vector. The double vector must be of size
n-by-1, where n is the number of
rows to be inserted.
To reduce conversion time, convert dates to serial date numbers using
datenum before calling
fastinsert.
The value of the AutoCommit property in the connection object
determines whether fastinsert automatically
commits the data to the database.
To view the AutoCommit value, access
it using the connection object; for example, conn.AutoCommit.
To set the AutoCommit value, use
the corresponding name-value pair argument in the database function.
To commit the data to the database, use the commit function or issue an SQL COMMIT statement
using the exec function.
To roll back the data, use rollback or
issue an SQL ROLLBACK statement using the exec function.
If an error message like the following appears when you run
fastinsert, the table might be open in edit
mode.
[Vendor][ODBC Product Driver] The database engine could not lock table 'TableName' because it is already in use by another person or process.
In this case, close the table in the database and rerun the
fastinsert function.
To export MATLAB data into a database, you can use the datainsert and insert functions. For maximum performance, use
datainsert.
fastinsert function will be removedNot recommended starting in R2018a
The fastinsert function will be removed in a future release.
Use the sqlwrite function instead. Some differences between the workflows
require updates to your code.
In prior releases, you exported data from the MATLAB workspace into a database by using the
fastinsert function and four input arguments. For
example:
tablename = 'productTable'; colnames = {'productNumber','stockNumber','supplierNumber', ... 'unitCost','productDescription'}; insertdata = {4,500565,1010,20,'Cooking Set'}; insertdata = cell2table(insertdata,'VariableNames',colnames) fastinsert(conn,tablename,colnames,insertdata)
Now the sqlwrite function requires only three input
arguments.
tablename = 'productTable'; colnames = {'productNumber','stockNumber','supplierNumber', ... 'unitCost','productDescription'}; insertdata = {4,500565,1010,20,'Cooking Set'}; insertdata = cell2table(insertdata,'VariableNames',colnames) sqlwrite(conn,tablename,insertdata)
close | commit | database | insert | logical | rollback | select | sqlwrite
You have a modified version of this example. Do you want to open this example with your edits?