transformations:exportdb
Differences
This shows you the differences between two versions of the page.
Next revision | Previous revision | ||
transformations:exportdb [2017/05/01 23:11] – created dmitry | transformations:exportdb [2023/10/14 16:20] (current) – update to latest wiki style craigt | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ===== Export into database table ===== | + | {{ transformations: |
+ | ====== | ||
+ | Category: Export / Database\\ | ||
- | Exports | + | \\ |
+ | =====Description===== | ||
+ | This action exports the current dataset into a database table. | ||
- | Export is performed using SQL INSERT statements by batches of 10 or 100 or 1000 rows. Exporting in bigger batches can be slightly faster on narrow tables (i.e. tables with few fields). For wide tables (i.e. table with hundreds of fields) or when fields contain long strings use smaller batches. | + | Note that the operation |
- | + | ||
- | The operation | + | |
- | Note that the operation effectively | + | \\ |
+ | =====Action settings===== | ||
+ | ^Setting | ||
+ | |Connector< | ||
+ | |||
+ | < | ||
+ | |||
+ | \\ | ||
+ | ====Export settings==== | ||
+ | ^Setting | ||
+ | |Table name< | ||
+ | |Column mapping |Select the method for mapping the dataset columns to the fields in the database table.\\ | ||
+ | |||
+ | < | ||
+ | |||
+ | \\ | ||
+ | ====Options settings==== | ||
+ | ^Setting | ||
+ | |When value doesn' | ||
+ | |When exporting a batch fails|Choose how EasyMorph will handle situations when the export fails.\\ | ||
+ | |||
+ | \\ | ||
+ | ====Custom SQL settings==== | ||
+ | ^Setting | ||
+ | |Execute custom SQL before exporting|To run an ad hoc SQL process on the data prior to exporting, check this setting //on// and enter an SQL statement. | ||
+ | |||
+ | |||
+ | \\ | ||
+ | =====Remarks===== | ||
+ | ===Performance=== | ||
+ | Under the hood, the exporting is performed using SQL INSERT statements by batches of 10, 100, 1'000, or 10'000 rows. Exporting in bigger batches can be faster and is recommended for narrow tables (i.e. tables with few fields). For wide tables (i.e. tables with hundreds of fields) or when columns contain long strings | ||
+ | |||
+ | Some databases have a performance bottleneck for batches of SQL INSERT statements which may lead to slow performance (especially on wide tables). Consider using the [[transformations: | ||
+ | |||
+ | \\ | ||
+ | ===Data type conversion=== | ||
+ | Exporting will convert cell values only in the following cases: | ||
+ | |||
+ | * Boolean cell to a numeric column exported as 1 (TRUE) or 0 (FALSE) | ||
+ | * Numeric cell to a date or a date/time column - the cell value is converted to a date or a date-time literal according to the SQL dialect of the target database | ||
+ | * Any cell to a text column or a column of unknown type is exported as a text literal | ||
+ | * Error cells are always exported as NULL | ||
+ | |||
+ | In all the other cases when the cell type doesn’t match the target column type either a NULL value is exported, or the exporting fails, depending on action settings. | ||
+ | |||
+ | \\ | ||
+ | =====Community examples===== | ||
+ | * [[http:// | ||
+ | |||
+ | \\ | ||
+ | =====See also===== | ||
+ | * [[transformations: | ||
+ | * [[transformations: | ||
+ | * [[transformations: | ||
- | Some databases have performance bottleneck for batches of SQL INSERT statements which may lead to slow performance (especially on wide tables). Consider using bulk load statements in [[sqlcommand|Database command]] transformation when exporting more than 1 million rows at once. See [[http:// |
transformations/exportdb.1493694674.txt.gz · Last modified: 2017/05/01 23:11 by dmitry