Skip to content

TSTool / Command / DeleteDataStoreTableRows

Overview

The DeleteDataStoreTableRows command deletes rows from a database datastore table by using one of the following methods:

  • Execute an SQL DELETE statement to delete all rows. Currently, only the ability to delete all rows is supported (see DeleteAllRows=True command parameter). In the future functionality will be implemented to delete specific rows using an SQL WHERE clause. See also the RunSql() command, which can be used to execute any SQL statement on a database datastore.
  • Execute an SQL TRUNCATE statement (see DeleteAllRows=Truncate). Although database software varies, this typically has the effect of resetting auto-number counters.

If database datastore support is not specifically provided by TSTool, a generic datastore can be used (see the Generic Database DataStore appendix). This command cannot be used with web service datastores and use with Excel datastores has not been tested. This command is useful in particular for bulk data processing such as to delete records in a table before (re)loading in bulk (see WriteTableToDataStore and WriteTimeSeriesToDataStore commands).

General constraints are as follows:

  • the table or views being processed must be writeable by the user specified for the database connection (some databases restrict direct access to data and/or require using stored procedures)

Command Editor

The command is available in the following TSTool menu:

  • Commands / Datastore Processing

The following dialog is used to edit the command and illustrates the syntax of the command.

DeleteDataStoreTableRows command editor

DeleteDataStoreTableRows Command Editor (see full-size image)

Command Syntax

The command syntax is as follows:

DeleteDataStoreTableRows(Parameter="Value",...)

Command Parameters

Parameter              Description Default                           
DataStore
required		 The name of a database datastore to process. None – must be specified.
DataStoreTable
required		 The name of the database table or view being processed. None – must be specified.
DeleteAllRows Indicate whether all rows should be deleted:
  • False – currently a safeguard. A future enhancement will enable deleting only rows that match specified table rows via a constraint.
  • True or – delete all rows using SQL DELETE statement
  • Truncate – delete all rows using SQL TRUNCATE statement.
 False – consequently, because specific deletes are not enabled, this is a safeguard against any deletes

Examples

See the automated tests.

Troubleshooting

See the main TSTool Troubleshooting documentation.

See Also