Element Details: Load+DB+Table.Insert
 Back


Available in: Logi Info Source code name: LoadDbTable.Insert

Inserts a row in an existing database table.

Requires a ColumnMap element to define the relationship between the input and database table columns.

Setting the EtlElementID attribute to the ID of an earlier Extract or Transform element determines which datalayer is loaded into the destination tables. If the EtlElementID is not specified, then the Load element will attempt to use the last Extract or Transform datalayer generated.

You can reference the number of rows affected with an ETL token.

For Example:
@Etl.myLoadID.rdRowsAffected~

Element Group:Load Data Elements



ATTRIBUTES

Click attribute Name to drill down for more information.

NamePossible ValuesDescription
AddErrorMessage
True
False
Adds two new columns to each row of the exception data. rdErrorMessage is the reason the row could not be processed. rdErrorSource is name of the source that returned the error.
BatchSize
UserDefined
The BatchSize attribute can be used to control the number of items (or rows) of a single database transaction. You can optimize your database commands to fit your specific configuration. If set to zero, the system will process the entire datalayer as a single transaction. BatchSize defaults to 1,000 rows per transaction.
BulkParameters
UserDefined
Additional custom parameters that will be added to the bulk API call done by the engine. The string is added to the end of either the SQL Command or command line depending on the bulk system used by the destination system. This attribute is only used when the InsertMethod is set to 'Bulk'.
ConnectionID
UserDefined
Specifies a connection to a data source that is defined in the Settings. For elements connecting to relational databases, default is the first Connection element in _settings.lgx.
DateFormat
UserDefined
Allow the user to define a custom date format string to use during database loads. If left blank in a LoadDbTable, the system will default to: "yyyy-MM-dd HH:mm:ss" for MySQL "MM/dd/yyyy HH:mm:ss" for Microsoft SQL "MM/DD/YYYY HH24:MI:SS" for Oracle SQL If left blank in a FixedField element, the system will default to: "yyyy-MM-dd HH:mm:ss"
EtlElementID
UserDefined
Enter the ID of the Extract or Transform element that contains the data to be processed. If this attribute does not have a value, then the system will use the data from the last Extract or Transform element.
ExceptionDataID
UserDefined
Defines an ETL ID value that can be used to address the exception data that was removed by the filter. The value of this attribute may be referenced by a DataLayer.ETL, LoadDbTable, SaveData, or any other element that supports the EtlElementID attribute.
FieldDelimiter
UserDefined
The character or string used to separate each field in a text file. For best results, the character used should not be found within the data. If left blank the default delimiter is "|". Use "Tab" to create a tab delimited file or "," to create a comma delimited file. "VerticalTab", "BackSpace", "FormFeed", as well as any character or string can also be used to create delimited files. NOTE: This attribute is only used with bulk loading.
ID
UserDefined
(Required) The ID attribute is a pervasive attribute that uniquely identifies an element within a definition file. The ID needs to be a unique value within the definition.
InsertMethod
Bulk
Batch
Used to define the method used to insert data into the database table. If left blank, then the system defaults to the "Batch" method. "Batch" will cause the system to load the records using mulitiple insert statements. After each batch of commands is sent the database system will update any existing indexes and fire any defined triggers. "Bulk" will cause ETL to use the high speed bulk loading API provided by the destination database system. ETL will automatically generate any required format and data files. Bulk loading is currently available for Microsoft SQL, MySQL, and Oracle database servers. Many bulk loading APIs do not provide support for complex data types such as image or blob.
MaxErrors
UserDefined
The maximum number of errors allowed before the command will be stopped. The default is no limit.
RowDelimiter
UserDefined
The character or string used to terminate each row of data in a text file. For best results, the character used should not be found within the data. If left blank the default delimiter is a line feed (Lf). Lf = End each line of data with just a line feed (Default). Cr = End each line of data with just a carriage return. CrLf = End each line with a carrage return and line feed.
TableName
UserDefined
(Required) The name of a database table.
TextQualifier
UserDefined
If given, then values having this symbol at the beginning and end are treated as text: 1) the text qualifier symbol is removed from the beginning and end 2) if the text qualifier symbol is doubled within the value, it is replaced with a single text qualifier symbol example using single quote as text qualifier: 144, 12/18/2004, 'Lee''s House of Kebab', '719 High Street' 144 12/18/2004 Lee's House of Kebab 719 High Street The default is a quote mark (").
TextQualifierMode
On
Off
Auto
Auto: (Default) If the value contains the delimiter, then the text will be enclosed within the TextQualifier. On: All values will be surrounded by qualifiers. Off: No values will be surrounded by qualifiers.



PARENT ELEMENTS

Click element to drill down for more information.

Etl Job
If
If ETL Error
If File Exists
If Folder Exists Loop.Data Layer Rows


CHILD ELEMENTS

Click element to drill down for more information.

Column Map
If ETL Error
MySql Custom Bulk Command
Oracle Custom Control File
PostgreSQL Custom Bulk Command
Sql Server Custom Format File


 Back to top


 Chart Debug