Load Query table
The
DEPM_MOD_QUERY table represents a SQL statement that is used to
either fill data to a table from a data connection or to execute any transformational
statement on the staging database.| Name | Definition |
|---|---|
| Name | nvarchar(50) NOT NULL; PK |
| Description | nvarchar(150) NULL |
| ModelName | nvarchar(50) NOT NULL; PK |
| TableName | nvarchar(50) NOT NULL; PK |
| Definition (can contain Custom Settings) | ntext NULL |
| DataConnectionName | nvarchar(50) NULL |
| ExistingRowsHandling | int NULL |
| LastSuccessfulLoad | timestamp NULL |
The name of a query is unique for the table and the model.
The query must be a Select query that is run on a database that is indicated by
DataConnectionName. The result of the query is inserted into the
referenced table.
ModelName references a model.
TableName references a table of the Staging database.
If DataConnectionName is NULL then this is interpreted as a reference to the
Staging database.
These values for ExistingRowsHandling indicate how conflicting row inserts
are handled when a query is run:
- NULL: Default. The value is 1 if the table has a primary key. The value is 0 if there is no primary key .
- 0: The option does not apply because the table does not have a primary key.
- 1: Existing rows are updated with new column values
- 2: Existing rows are skipped.
- 3: The query is rolled back and the target table is not updated by the query.
LastSuccessfulLoad indicates the time (UTC) when the query was last run with
a successful result.
The table has these references:
| Source columns | Target table | Target columns |
|---|---|---|
| ModelName | _model | Name |
| TableName | _table | Name |