In this blog post, I’ll provide an explanation why you should avoid using the CREATE TABLE AS SELECT statement.
The SQL statement “create table <table_name> as select …” is used to create a normal or temporary table and materialize the result of the select. Some applications use this construct to create a copy of the table. This is one statement that will do all the work, so you do not need to create a table structure or use another statement to copy the structure.
At the same time there are a number of problems with this statement:
- You don’t create indexes for the new table
- You are mixing transactional and non-transactional statements in one transaction. As with any DDL, it will commit current and unfinished transactions
- CREATE TABLE … SELECT …