Questions tagged [ctas]

`CREATE TABLE AS Select` (CTAS) and its associated syntax creates a new table by performing the following - Run a CREATE TABLE using the table definition of every column in the SELECT - Populating the new table with the result set of the SELECT NOTE : The table created has no indexes or constraints.

Filter by
Sorted by
Tagged with
3
votes
1answer
2k views

PostgreSQL: Why is CREATE TABLE AS faster than CREATE … INSERT INTO?

Below are the two different syntaxs for the same thing. With a COPY TABLE AS SELECT (CTAS). CREATE TABLE main AS SELECT * FROM other; As separate statements with CREATE TABLE and INSERT INTO ...
1
vote
1answer
355 views

Lock a table for a CTAS upsert in Azure SQL DataWarehouse

I have a type 2 dimension in Azure SQL Data Warehouse. Essentially I am creating an interim table with CREATE TABLE myDimension_temp AS SELECT ... FROM myStagingTable; etc After the CTAS is ...
0
votes
1answer
56 views

When does a table get an oid?

I'm wondering when a table acquires an oid. Is it only when the transaction commits? Or does it have a hidden oid prior to commit? Especially in the context of a CREATE TABLE AS SELECT.
1
vote
1answer
590 views

How do I eliminate a second seq scan over a table when deriving a new table?

Let's say I have some sample data, 100 million rows. CREATE TEMP TABLE foo AS SELECT id, md5(id::text), trunc(random()*1e6) FROM generate_series(1,1e6) AS t(id); This will generate a table like ...
5
votes
1answer
3k views

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for ...
-1
votes
1answer
391 views

ORA-01536: Out of space error when doing CTAS

I have two schemas A and B. I'm trying to copy over table B.users to A. I've followed these steps: Run in schema B: GRANT ALL ON users TO A; Run in schema A: CREATE TABLE users AS (SELECT * FROM ...
2
votes
1answer
314 views

How do you CREATE TABLE AS SELECT (CTAS) with a composite type?

PostgreSQL supports CREATE TABLE AS SELECT (CTAS). It also supports composite types that can represent whole rows. Here is an example of a CTAS, CREATE TABLE foo AS SELECT * FROM ( VALUES (1), (2) );...
15
votes
2answers
6k views

CREATE TABLE AS vs SELECT INTO

PostgreSQL supports CREATE TABLE AS and SELECT INTO when do I use both? CREATE TABLE AS -- define a new table from the results of a query CREATE TABLE AS creates a table and fills it with data ...
1
vote
1answer
1k views

MySQL “CREATE TABLE LIKE” include triggers

I'm using MySQL 5.6 (5.6.29-76.2-56-log Percona XtraDB Cluster) When I issue a CREATE TABLE LIKE the triggers on the tables are not copied - is it possible to get them on the new table as well?
1
vote
1answer
1k views

CREATE TABLE AS SELECT creates non-nullable columns

I am using the CREATE TABLE AS SELECT statement in Oracle 11g to get data from SQL Server 2012 via a database link. Oracle creates all these tables with non-nullable columns and that causes me ...
0
votes
2answers
461 views

Table JOIN and CREATE new table extremely slow

I'm using something like this to create a new table: CREATE TABLE result AS (SELECT calls.*, targ_with_planned_calls.* FROM calls INNER JOIN planned ON calls.first_id = ...
12
votes
1answer
17k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select `user`.`...
10
votes
2answers
7k views

MySQL Locks while CREATE TABLE AS SELECT

I am running the following (dummy) query CREATE TABLE large_temp_table AS SELECT a.*, b.*, c.* FROM a LEFT JOIN b ON a.foo = b.foo LEFT JOIN c ON a.bar = c.bar Suppose the query ...