When working with SQL Server, there are two features that can be used to create tables based on a SELECT statement: CETAS (Create External Table As Select) and CTAS (Create Table As Select). In this article, we will explore these features and provide examples of how they can be used.

CTAS: Create Table As Select

The CTAS feature is similar to the SELECT INTO clause. It allows you to create a copy of a table based on a SELECT statement. Let’s take a look at an example:

SELECT *
INTO DimProductSubcategoryIntoTest
FROM dbo.DimProductSubcategory

In this example, a new table named DimProductSubcategoryIntoTest will be created based on the SELECT statement. The new table will have the same structure and data as the original table.

By default, a CTAS operation creates a table with a clustered index and statistics. However, it is also possible to change the index type or the distribution. Here’s an example:

CREATE TABLE DimProductSubcategoryCTAS
WITH
(
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)
AS
SELECT *
FROM dbo.DimProductSubcategory

In this example, a new table named DimProductSubcategoryCTAS will be created with a Round Robin distribution and a clustered columnstore index.

It’s important to note that the CTAS operation is fully parallelized, allowing for efficient processing of large datasets.

CETAS: Create External Table As Select

The CETAS feature allows you to create a table based on a SELECT statement from an external database. Here are the steps to create an external table using CETAS:

1. Upload a file with data to Azure Blob Storage.
2. Create a master key in the database to encrypt the data.
3. Create a credential using the access key in Azure.
4. Create an external data source to specify the path of the file in Azure.
5. Create an external file format to specify the format of the file.
6. Populate the new external table using a SELECT query.

For a detailed explanation of each step, please refer to the references section at the end of this article.

Here’s an example of how to create an external table using CETAS:

CREATE EXTERNAL TABLE hdfsCustomer
WITH
(
    LOCATION = '/customer.tbl',
    DATA_SOURCE = listcustomers,
    FILE_FORMAT = myformat
)
AS
SELECT [CustomerKey], [FirstName], [LastName], [EmailAddress]
FROM dbo.DimCustomer

In this example, an external table named hdfsCustomer will be created based on the SELECT statement. The table will be populated with data from the DimCustomer table in the database.

As you can see, the CETAS feature allows for seamless integration with external databases, making it easier to work with data from different sources.

Conclusion

In this article, we explored the CTAS and CETAS features in SQL Server. These features provide a convenient way to create tables based on SELECT statements and integrate with external databases. Whether you need to create a copy of a table or work with data from an external source, CTAS and CETAS can help simplify your workflow.

If you have any questions or comments, feel free to leave them below.

References

• CREATE EXTERNAL TABLE AS SELECT
• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• Create Table As Select (CTAS) in SQL Server