User Tools

Site Tools


transformations:exportdb

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Last revision Both sides next revision
transformations:exportdb [2017/05/02 03:11]
dmitry created
transformations:exportdb [2020/01/22 17:59]
dmitry
Line 1: Line 1:
 ===== Export into database table ===== ===== Export into database table =====
  
-Exports current dataset into database table which name is specified either explicitly or using a [[:parameters|project parameter]]. The table must already exist. If the table doesn't exist it can be created using [[sqlcommand|Database command]] transformation prior to exporting data into it.+Exports the current dataset into the database table which name is specified either explicitly or using a [[:parameters|parameter]]. The table must already exist. If the table doesn't exist it can be created using the [[sqlcommand|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 - 1 or 0 will be exported 
 +  * Numeric cell to a date or a datetime column - source number will be treated as an OADate and converted to a date or a datetime literal according to the format, supported by the target database 
 +  * Any cell to a text column or a column of unknown type - will be exported as text 
 +  * Error cell will be always exported as null 
 + 
 +In all the other cases when cell type doesn’t match column type a NULL value will be exported.
  
-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. 
    
 The operation is //atomic// meaning that it either successfully exports all rows, or nothing (in case of failure). 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 [[sqlcommand|Database command]] transformation.+Note that the operation effectively //appends// rows to existing ones. If you need to remove old data use the [[sqlcommand|Database command]] action. 
 + 
 +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 the [[sqlcommand|Database command]] action when exporting more than 1 million rows at once.  
 + 
 +==See also==
  
-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://easymorph.com/learn/export-database.html|Tutorial: exporting data into a database table]]+  * [[http://easymorph.com/learn/export-database.html|Tutorial: exporting data into a database table]] 
 +  * [[transformations:sqlcommand|SQL Command]]
transformations/exportdb.txt · Last modified: 2020/01/22 18:04 by dmitry