When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The provider updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.
See Caching Data for more information on different caching strategies.
CACHE Statement Syntax
The cache statement may include the following options that alter its behavior:
CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement>
If this option is set, the provider removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.
If this option is set, the provider commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.
If this option is set, the provider drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.
If this option is set, the provider creates the cache table based on the SELECT statement without executing the query.
If this option is set, the provider alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.
Use the following cache statement to cache all rows of a table:
CACHE SELECT * FROM Schemas
Use the following cache statement to cache all rows of a table into the cache table CachedSchemas:
CACHE CachedSchemas SELECT * FROM Schemas
Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.
CACHE CachedSchemas SELECT * FROM Schemas WHERE DateModified > '2013-04-04'
Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only Type and DatasetName even though the cache table CachedSchemas has all the columns in Schemas.
CACHE CachedSchemas SCHEMA ONLY SELECT * FROM Schemas CACHE CachedSchemas SELECT Type, DatasetName FROM Schemas