Devart MySql Destination is designed for loading data into MySQL tables.
For data migration to the MySQL database, Devart MySql Destination component uses INSERT statements that insert data by several rows at the same time.
The MySQL table you want to migrate data to can be selected in the Table Name combo box on the Component Properties tab of the Devart MySql Destination data flow object.
The action that should be performed with data during migration is defined by the Action parameter of Devart MySql Destination object, that provides the following options: Insert, Update, Delete, and BulkInsert. As all of the actions have certain peculiarities:
- Insert - as this case is the most common, we decided to cover it in a detailed tutorial. It uses simple INSERT statements to load data to MySQL.
- BulkInsert - uses MySQL-specific technique to quickly load data to MySQL. For data migration to the MySQL database, Devart MySql Destination component uses INSERT statements that insert data by several rows at the same time.
- Update - Devart MySql Destination component in the Update mode enables updating
data in a MySQL table.
Note:Peculiarity of this action consists in the fact, that performing it requires using primary key values of the MySQL table. Thus, when performing an update, you must know and specify the primary key values of the MySQL table to update a record.
- Delete - Devart MySql Destination component enables deleting data from a
MySQL table.
Note:
As is the case with the Update action, the ID column from the local data source corresponding to the Primary key values column in the MySQL table, must be used.
The amount of data that can be migrated at a time is limited by the BufferSize property.
BufferSize defines the maximum size of the local buffer, its default value is 262144.
Additionally, Devart MySql Destination allows locking the table for the bulk data loading operation with the BulkInsertLock property and enabling the use of INSERT DELAYED statements instead of usual INSERT with the BulkInsertDelayed property.
Devart MySQL Data Flow components support error outputs, which allow you to manage how the component treats row-level errors in both incoming and outgoing data.
The error output includes the column set taking part in the data flow and additional columns, they are ErrorCode that identifies the error, ErrorColumn that contains the lineage identifier of the error column and ErrorDescription that provides a detailed description of the error.
The ErrorCode column can accept the following values:
| ErrorCode | Description |
|---|---|
| -1 | Means that the record is valid. |
| >0 | Means that the record causes an error. |
Additionally, when the primary key values are automatically generated for the target table, Devart MySql Destination retrieves the generated values and adds them to its output columns.