Menu
Simba Technologies
Simba Technologies

SimbaEngine X SDK 10.1.3
Developing Drivers for Data Stores Without SQL

Create Table As Select (CTAS)

In SQL, you can use the CREATE TABLE AS SELECT statement to copy the contents of an existing table or tables into a new table. The CTAS statement creates a new table based on the output of a SELECT statement. The Simba SQLEngine allows you to support CTAS commands in your custom ODBC driver.

Example CTAS statement:

The following statement copies the table Employee to the table NewTable:

CREATE TABLE NewTable AS SELECT * FROM Employee

Note:

The command CREATE TABLE AS SELECT is also called CREATE TABLE AS.

Level of support for CTAS

The SimbaEngine X SDK supports the SQL 2003 specification for CREATE TABLE AS, with the following exceptions:

  • table distribution options are not supported
  • union, except and intersect select options are not supported

Implementing CTAS in your custom ODBC Driver

To implement CTAS in your driver, your driver must have read-write capability. As well, your driver must implement the MyDataEngine::CreateTable() method, where MyDataEngine is your DSIExtSqlDataEngine-derived class.

When a CTAS SQL command is received, the SimbaEngine X SDK calls your driver's MyDataEngine::BeginCreateTable(). Then, the SimbaEngine X SDK passes in a table specification. The BeginCreateTable() method must return an object of a class that implements ITableTemplate. The SimbaEngine X SDK fills in the table template, then calls Instantiate() on the template. Your driver must provide the implementation of Instantiate().

Example - Implementing CTAS in Your Driver

  1. Change your MyTable class to extend ITableTemplate in addition to any other classes that it extends.
  2. Implement the MyTable::Instantiate() method. This method should ensure that the table is ready to be used.
  3. Update the MyDataEngine class to implement the BeginCreateTable() method.