INSERT INTO

The INSERT INTO statement populates a local cube with dimension members. If the local cube is in MOLAP storage mode, the INSERT INTO statement also populates the local cube with data. The INSERT INTO statement is used after a CREATE CUBE statement to create a local cube.

BNF

<insert-into-statement> ::= INSERT INTO <target-clause>
  [<options-clause>] [<bind-clause>] <source-clause>

<target-clause> ::= <cube-name> <open-paren> <target-element-list>
  <close-paren>

<target-element-list> ::= <target-element>[, <target-element-list>]

<target-element> ::= [<dim-name>.[<hierarchy-name>.]]<level-name>
  | <time-dim-name>
  | [Measures.]<measure-name>
  | SKIPONECOLUMN

<level-name> ::= <simple-level-name> | <simple-level-time>.NAME | <simple-level-time>.KEY

<time-dim-name> ::= <dim-name-type-time> | <dim-name-type-time>.NAME | <dim-name-type-time>.KEY

<options-clause> ::= OPTIONS <options-list>

<options-list> ::= <option>[, <options-list>]

<option> ::= <defer-options> | < analysis-options>

<defer-options> ::= DEFER_DATA | ATTEMPT_DEFER

<analysis-options> ::= PASSTHROUGH | ATTEMPT_ANALYSIS

<bind-clause> ::= BIND (<bind-list>)

<bind-list> ::= <simple-column-name>[,<simple-column-name>]

<simple-column-name> ::= <identifier>

<source-clause> ::= SELECT <columns-list>
  FROM <tables-list>
  [ WHERE <where-clause> ]
  | DIRECTLYFROMCACHEDROWSET <hex-number>

<columns-list> ::= <column-expression> [, < columns-list> ]

<column-expression> ::= <column-expression-name>

<column-expression-name> ::= <column-name> [AS <alias-name>]
  | <alias name> <column-name>

<column-name> ::= <table-name>.<column-name>
  | <column-function> | <ODBC scalar function> | <braced-expression>

<column function> ::= <identifier>(. . .)

<ODBC scalar function> ::= {FN<column-function>}

<braced-expression> ::= (. . .)

<tables list> ::= <table-expression> [, <table-list>]

<table-expression> ::= <table-name> [ [AS] <table-alias>]

<table-alias> ::= <identifier>

<table-name> ::= <identifier>

<where clause> ::= <where-condition> [AND <where-clause>]

<where condition> ::= <join-constraint> | <application constraint>

<join-constraint> ::= <column-name> = <column-name>
  | <open-paren><column-name> = <column-name><close-paren>

<application-constraint> ::= (. . .)
  | NOT (. . .)

   | (. . .) OR (. . .)

<identifier> ::= <letter>{<letter>|<digit>|<underline>|<dollar>|<sharp>}. . .

Remarks

• Names of elements in an INSERT INTO statement are level and measure names, sometimes qualified with dimension name or the keyword Measures to avoid ambiguity. The Measures keyword is case-sensitive in binary comparisons. If you use binary comparison or are unsure of your comparison method, use Measures as shown with only “M” in upper case.
• Each level and each measure in a cube is derived from a column in the SELECT clause.
• The columns specified in the associated SELECT clause are bound to the elements of the INSERT INTO statement in the order specified and in a one-to-one relationship.
• Each level can be derived from two columns, with one used as a name column and the other used as a key column. Both columns must be in the same table. If there are two columns associated with a level, suffix .NAME or .KEY should be used in the INSERT INTO statement after the level name.
• If a column specified in the SELECT clause does not have a related element in the INSERT INTO statement, the keyword SKIPONECOLUMN can be used in the INSERT INTO statement as a placeholder for the unused column. SKIPONECOLUMN can be used more than once.
• In the INSERT INTO statement, a dimension of TYPE TIME can be specified by the name of the dimension. The dimension name is used to correlate the entire dimension with a single column in the source table that contains data with a date/time data type. The levels, of TYPE <level type>, identified for the time dimension in the CREATE CUBE statement cause the time information to be extracted from the source column specified in the SELECT clause. See Example 4 later in this topic.
• The WHERE clause can have both application and join constraints. The parser will parse only join constraints. It uses the join constraint to find a path from all tables to the fact table and to the dimension tables. The application constraint is used only to specify constraints on a fact table and is passed through without modifications.
• Expressions between parentheses are considered to be application constraints. For example, if the expression Sales.Product_ID = Products.Product_ID AND Sales.Customer_ID = Customers.Customer_ID is enclosed in parentheses, it is treated as an application constraint and is not used as a join constraint. It is the responsibility of the client application to ensure that parentheses are used only around application constraints. For example, (Product.Price < 100 AND Product.Category = 1).
• The BIND clause is used to bind level and measure names specified in the INSERT INTO statement with column names used to create rowsets.
• The AS <alias-name> syntax is not supported for local ROLAP cubes.

Example 1

INSERT INTO MyCube (Year, Month.Name, Month.Key, [Product Group], [Product Name], Country, Sales, Cost)

OPTIONS DEFER_DATA

SELECT MyTable.Year, MyTable.Month, MONTH(MyTable.Month), MyTable.ProdGroup, MyTable.ProdName, MyTable.Country, MyTable.Sales, MyTable.Cost

FROM MyTable

WHERE MyTable.SalesRep = "Amir" and MyTable.CustomerGroup = "Industry"

Example 2

INSERT INTO MyCube (Year, Month, [Product Group], [Product Name], Country, Sales, Cost)

OPTIONS PASSTHROUGH SELECT MyTable.Year, MyTable.Month, MyTable.ProdGroup, MyTable.ProdName, MyTable.Country, MyTable.Sales, MyTable.Cost

FROM MyTable

WHERE MyTable.SalesRep = "Amir" and MyTable.CustomerGroup = "Industry"

Note The PASSTHROUGH option specifies that the SELECT clause that follows it is to be passed directly to the database engine with no parsing by PivotTable® Service.

Example 3

INSERT INTO MyCube (Year, Month, [Product Group], [Product Name], Country, Sales, Cost)

DIRECTLYFROMCACHEDROWSET 0x00001284

Note The DIRECTLYFROMCACHEDROWSET keyword directs data to be read from the address in memory identified immediately after the keyword. It is the responsibility of the client application to specify the correct address in memory. At run time, the number is assumed to be the in-process address of an IUnknown pointer to an OLE DB rowset.

Example 4

CREATE CUBE MyCube (

DIMENSION TimeDim TYPE TIME,

   LEVEL MyYear TYPE YEAR,

   LEVEL MyQtr TYPE QUARTER,

   LEVEL MyMonth TYPE MONTH,

DIMENSION Products,

   LEVEL [Product Group],

   LEVEL [Product Name],

DIMENSION Geography,

   LEVEL State,

   LEVEL City,

MEASURE [Sales]

   FUNCTION SUM

   FORMAT ‘Currency’,

MEASURE [Units Sold]

   FUNCTION SUM

)

INSERT INTO MyCube (TimeDim, [Product Group], [Product Name], State, City, Sales, [Units Sold])

OPTIONS DEFER_DATA

SELECT MyTable.TransDate, MyTable.ProdGroup, MyTable.ProdName, MyTable.State, MyTable.City, MyTable.Sales, MyTable.UnitsSold

FROM MyTable

WHERE MyTable.SalesRep = "Jacobsen" and MyTable.CustomerGroup = "Industry"

Passthrough and Advanced Query Processing

The PASSTHROUGH option causes the SELECT clause to be passed directly to the source database without modification by PivotTable Service. If PASSTHROUGH is not specified, PivotTable Service parses the query and formulates a set of queries equivalent to the original that is optimized for the source database and index structures. This set of queries is often more efficient than the specified query.

The DEFER_DATA option causes the query to be parsed locally and executed only when necessary to retrieve data to satisfy a user request. DEFER_DATA is used to specify that a local cube is defined in the ROLAP storage mode.

The ATTEMPT_DEFER option causes PivotTable Service to attempt to parse the query and defer data loading if successful, or, if the query cannot be parsed, to process the specified query immediately as if the PASSTHROUGH had been specified.

The ATTEMPT_ANALYSIS option causes PivotTable Service to attempt to parse the query and formulate an optimized set of queries (process in the MOLAP mode), or, if the query can’t be parsed, to process the specified query immediately as if the PASSTHROUGH had been specified.

Passthrough Compatibility Matrix

In the following matrix, “PT” indicates passthrough.