This topic describes the syntax for programmatically deleting data from the Data Warehouse.
A Data Deletion Data Transformation Services (DTS) task is provided for deleting all the data in the Data Warehouse, or for deleting Web log import data by the TaskHistoryID property. For information about the Data Deletion DTS task, see Deleting Data from the Data Warehouse.
The delete syntax format is:
DELETE [FROM] BaseClassName [WITH (DELETEORPHANS | ONLYFACT | UPDATECHILDREN)] [WHERE where_clause]
The name of the class containing the data to delete.
Specifies to delete any orphans produced by the delete operation. An orphan is an instance in a child class that is not referenced by any of its parent classes.
Specifies to only delete children of BaseClassName that are marked as FACT tables.
Specifies to update the children, if any, of BaseClassName.
memberpath [NOT] operator testvalue [AND where_clause]
memberpath - [BaseClassName.][pathelements]MemberName
pathelements - (ParentName|RelationToParentName).[pathelements]
ParentName - the name of the class that is a parent to the class
immediately preceeding it in memberpath
RelationToParentName - the name of the relation between the parent
and its base class
MemberName - the name of a class member from the rightmost class in
memberpath; this specifies the class member to test in where_clause
operator - one of the following operators:
|=||<>||<||>||<=||<=||LIKE||IS LIKE||IS NULL||IS IN||NOT NULL|
|!=||!>=||!<=||!>||!<||NOT LIKE||IS NOT LIKE||IS NOT NULL||IS NOT IN||NOT IN|
testvalue - one of the following:
value, IN valuelist, NULL
value - the value to test MemberName against in where_clause
valuelist - a comma delimited list of values surrounded by parentheses
Set cmdDel = WScript.CreateObject("ADODB.Command.2.5") cmdDel.CommandText = "DELETE FROM Request" cmdDel.CommandText = "DELETE FROM Request WHERE RequestNum > 2" cmdDel.CommandText = "DELETE FROM Date WHERE DTimestamp > '1998-10-29 00:00:00'" cmdDel.CommandText = "DELETE FROM Date WITH UPDATECHILDREN" cmdDel.CommandText = "DELETE FROM Date WITH DELETEORPHANS" cmdDel.CommandText = "DELETE FROM Date WITH UPDATECHILDREN WHERE DTimestamp > '1998-10-29 00:00:00'" cmdDel.CommandText = "DELETE FROM Date WITH DELETEORPHANS WHERE Date.DTimestamp > '1998-10-29 00:00:00'" cmdDel.CommandText = "DELETE FROM Visit WHERE DTimestamp > 1998-10-29 00:00:00'" cmdDel.CommandText = "DELETE FROM Visit WHERE DTimestamp IN ('1999-10-29 00:00:00', '1999-10-30 00:00:00')" cmdDel.CommandText = "DELETE FROM Request WHERE Request.Loguser.IPRef.Hostname = " & _ "'220.127.116.11'" cmdDel.Execute
All rights reserved.