INSERT INTO (DMX)

Processes the specified data mining object. For more information about processing mining models and mining structures, see Processing Data Mining Objects.

If a mining structure is specified, the statement processes the mining structure and all its associated mining models. If a mining model is specified, the statement processes just the mining model.

Syntax

INSERT INTO [MINING MODEL]|[MINING STRUCTURE] <model>|<structure> (<mapped model columns>) <source data query>
INSERT INTO [MINING MODEL]|[MINING STRUCTURE] <model>|<structure>.COLUMN_VALUES (<mapped model columns>) <source data query>

Arguments

• model
  A model identifier.

• structure
  A structure identifier.

• mapped model columns
  A comma-separated list of column identifiers and nested identifiers.

• source data query
  The source query in the provider-defined format.

Remarks

If you do not specify MINING MODEL or MINING STRUCTURE, Analysis Services searches for the object type based on the name, and processes the correct object. If the server contains a mining structure and a mining model that have the same name, an error is returned.

By using the second syntax form, INSERT INTO*<object>*.COLUMN_VALUES, you can insert data directly into the model columns without training the model. This method provides column data to the model in a concise, ordered manner that is useful when you work with datasets that contain hierarchies or ordered columns.

If you use INSERT INTO with a mining model or a mining structure, and leave off the <mapped model columns> and <source data query> arguments, the statement behaves like ProcessDefault, using bindings that already exist. If bindings do not exist, the statement returns an error. For more information about ProcessDefault, see Processing Options and Settings. The following example shows the syntax:

INSERT INTO [MINING MODEL] <model>

If you specify MINING MODEL and provide mapped columns and a source data query, the model and associated structure is processed.

The following table provides a description of the result of different forms of the statement, depending on the state of the objects.

Statement	State of objects	Result
INSERT INTO MINING MODEL<model>	Mining structure is processed.	Mining model is processed.
	Mining structure is unprocessed.	Mining model and mining structure are processed.
	Mining structure contains additional mining models.	Process fails. You must reprocess the structure, and the associated mining models.
INSERT INTO MINING STRUCTURE<structure>	Mining structure is processed or unprocessed.	Mining structure and associated mining models are processed.
INSERT INTO MINING MODEL<model> that contains a source query or INSERT INTO MINING STRUCTURE<structure> that contains a source query	Either the structure or the model already contains content.	Process fails. You must clear the objects before you perform this operation, by using DELETE (DMX).

Mapped Model Columns

By using the <mapped model columns> element, you can map the columns from the data source to the columns in your mining model. The <mapped model columns> element has the following form:

<column identifier> | SKIP | <table identifier> (<column identifier> | SKIP), ...

By using SKIP, you can exclude certain columns that must exist in the source query, but that do not exist in the mining model. For example, in the nested table example later in this topic, SKIP excludes the OrderNumber column from training of the MyAssociationModel mining model. OrderNumber is used as a foreign key in the Models nested table, but does not exist in the Models table column in the mining model. It therefore does not train the model, and can be skipped.

Source Data Query

The <source data query> element can include the following data source types:

• OPENQUERY
• OPENROWSET
• SHAPE
• Any Analysis Services query that returns a rowset

For more information about data source types, see <source data query>.

Basic Example

The following example uses OPENQUERY to train a Naive Bayes model based on the targeted mailing data in the AdventureWorksDW database.

INSERT INTO NBSample (CustomerKey, Gender, [Number Cars Owned],
    [Bike Buyer])
OPENQUERY([Adventure Works DW],'Select CustomerKey, Gender, [NumberCarsOwned], [BikeBuyer] FROM [vTargetMail]')

Nested Table Example

The following example uses SHAPE to train an association model that contains a nested table.

INSERT INTO MyAssociationModel
    ([OrderNumber],[Models] (SKIP, [Model])
    )
SHAPE {
    OPENQUERY([Adventure Works DW],'SELECT OrderNumber
    FROM vAssocSeqOrders ORDER BY OrderNumber')
} APPEND (
    {OPENQUERY([Adventure Works DW],'SELECT OrderNumber, model FROM 
    dbo.vAssocSeqLineItems ORDER BY OrderNumber, Model')}
  RELATE OrderNumber to OrderNumber) 
AS [Models]

See Also

Reference

Data Mining Extensions (DMX) Data Definition Statements
Data Mining Extensions (DMX) Data Manipulation Statements
Data Mining Extensions (DMX) Statement Reference

Help and Information

Getting SQL Server 2005 Assistance