fastinsert
(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 Version History.
Description
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.
Examples
Insert Row into Table Using ODBC Driver
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 4stockNumber
equal to 500565supplierNumber
equal to 1010unitCost
equal to $20productDescription
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)
Insert Multiple Rows into Table
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)
Insert Numeric Data into Table
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)
Insert and Commit Data in Table
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)
Insert Boolean Data into Table
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)
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
colnames
— Database table column names
cell array of character vectors | string array
Database 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 insert
numeric matrix | cell array | table | dataset | structure
Data 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
.
Tips
The value of the
AutoCommit
property in theconnection
object determines whetherfastinsert
automatically commits the data to the database.To view the
AutoCommit
value, access it using theconnection
object; for example,conn.AutoCommit
.To set the
AutoCommit
value, use the corresponding name-value pair argument in thedatabase
function.To commit the data to the database, use the
commit
function or issue an SQLCOMMIT
statement using theexec
function.To roll back the data, use
rollback
or issue an SQLROLLBACK
statement using theexec
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.
Alternative Functionality
To export MATLAB data into a database, you can use the datainsert
and insert
functions. For maximum performance, use
datainsert
.
Version History
Introduced before R2006aR2018a: fastinsert
function will be removed
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)
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)