User Tools

Site Tools


Export into database table

Exports the current dataset into the database table which name is specified either explicitly or using a parameter. The table must already exist. If the table doesn't exist it can be created using the Database command action prior to exporting data into it.

Under the hood, the exporting 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 columns contain long strings use smaller batches.

Export 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 datetime 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 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 a NULL value is exported.

The operation is atomic meaning that it either successfully exports all rows, or nothing (in case of failure).

Note that the operation effectively appends rows to existing ones. If you need to remove old data use the Database command action.

Some databases have a performance bottleneck for batches of SQL INSERT statements which may lead to slow performance (especially on wide tables). Consider using bulk load statements with the Database command action when exporting more than 1 million rows at once.

See also
transformations/exportdb.txt · Last modified: 2020/01/22 18:04 by dmitry