User`s guide

6 Using Database Toolbox Functions
6-68
Inserting Data Using the fastinsert Function
In this section...
“About the fastinsert Function” on page 6-68
“Database Considerations” on page 6-69
About the fastinsert Function
• When working with a JDBC driver connection or a JDBC/ODBC bridge connection
established using the database function, fastinsert offers improved performance
over insert. insert creates and executes an SQL insert query for each row of
data. fastinsert creates the insert query only once and then allows for the data
values to be plugged in. All rows of data get inserted as a batch resulting in an overall
faster performance over insert. However, since fastinsert relies more on driver
functions compared to insert, it is possible in some edge case scenarios that the
driver functions do not work as expected. In such cases, insert might be preferred,
especially if the data to be inserted is small. datainsert is faster than fastinsert
but needs data to be formatted in a specific way and accepts cell arrays and numeric
matrices as input data.
• When working with a native ODBC connection established using the
database.ODBCConnection function, fastinsert and insert are identical.
datainsert is not supported for native ODBC connections.
• To insert dates and timestamps with the native ODBC interface, use the format
'YYYY-MM-DD HH:MM:SS.MS'.
• fastinsert provides up to millisecond precision for timestamps. For greater
precision, use datainsert.
• To reduce conversion time, convert dates to serial date numbers using datenum before
calling fastinsert.
• To insert data into a structure, table, or dataset array, use the following special
formatting. Each field or variable in a structure, table or dataset array must be a cell
array or double vector of size m-by-1, where m is the number of rows to be inserted.
• The status of the AutoCommit flag determines whether fastinsert automatically
commits the data to the database. Use get to view the AutoCommit flag status
for the connection and use set to change it. Use commit or issue an SQL commit
statement using exec to commit the data to the database. Use rollback or issue an
SQL rollback statement using exec to roll back the data.