INSERT INTO Statement

The INSERT INTO statement binds a logical cube to an existing relational view and populates this cube with dimension members and data.

BNF

<insert-statement> ::= INSERT INTO <target-clause> <property-list>
  [<options-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

<property-list> ::= <property>[, <property-list>]

<property> ::= <property-name> | <property-name> = <value>

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

<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

<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>}…

Examples

Example 1

INSERT INTO MyCube (Year, Month, [Product Group], [Product Name], Country, Sales, Cost)
OPTIONS DEFER_DATA
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"

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 key word specifies that the following SELECT statement is to be passed directly to the database engine with no parsing by Microsoft^® 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 in quotes immediately after the keyword. It is the responsibility of the client to specify the correct address in memory. At runtime, the number is assumed to be the in-process address of an IUnknown pointer to an OLEDB rowset.

INSERT INTO Notes

• Each level and each measure in a cube is derived from a column in the select statement.
  
  
• When using an INSERT INTO statement, the columns specified in the associated SELECT statement are bound to the elements of the INSERT INTO statement in the order specified and in a one-to-one relationship. If a column is specified in the SELECT statement that does not have a related element in the INSERT INTO statement, the keyword SKIPONECOLUMN may be used in the INSERT INTO statement as a placeholder for the unused column. SKIPONECOLUMN may be used more than once.
  
  
• Names of elements in an INSERT INTO are level and measures names, sometimes qualified with dimension name or the word MEASURES to avoid ambiguity.
  
  
• Automatically generated time dimensions must be specified as TYPE TIME in a CREATE CUBE statement, and each level of the dimension must specify TYPE <level-type>.
  
  
• The Where clause can have both application and join constraints. The Parser will parse only join constraints and will use 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 tables, and is passed to the query 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 will be treated as an application constraint and will not be used as a join constraint. It is the responsibility of the client to ensure that parentheses are used only around application constraints, such as (Product.Price < 100 AND Product.Category = 1).

Passthrough and Advanced Query Processing

The PASSTHROUGH option causes the SELECT query to be passed directly to the source database without modification or delay by Microsoft^® PivotTable^® Service. If PASSTHROUGH is not specified, Microsoft PivotTable Service parses the query and formulates an equivalent query that uses knowledge of data and index structures and 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. The ATTEMPT_DEFER option causes Microsoft 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 Microsoft PivotTable Service to attempt to parse the query and formulate an equivalent query (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