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.
17 questions
3
votes
0
answers
3k
views
Difference between To_Number (Null) and Cast (Null as Number) in Oracle
There is a difference in behavior between these two variants to create a view with a null number column and then a table from that view: One fails and one succeeds.
At the same time there is no ...
- 76
1
vote
1
answer
1k
views
How to find error rows in bulk insert?
MySQL 5.6: I'm experimenting with copying data from one table to another in different ways. The source table is on a remote server and has about 500,000 rows - I use the federated engine to connect. I ...
- 231
0
votes
1
answer
157
views
Reducing CREATE TABLE overhead?
I have a simple SQL query: SELECT * FROM table; - a simple sequential scan - which takes me 10s.
When I add CREATE TABLE AS (CTAS syntax) to it, i.e. my query is CREATE TABLE db_test AS SELECT * FROM ...
- 537
0
votes
1
answer
52
views
Enforcing lowest transaction isolation level across concurrent sessions?
I am trying to learn transactions better in PostgreSQL and came across this observation.
I have a situation where I am creating multiple tables using an SQL statement across multiple sessions. I am ...
- 537
5
votes
1
answer
7k
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
...
- 463
2
votes
2
answers
814
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 ...
- 21
0
votes
1
answer
183
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.
- 64.6k
1
vote
1
answer
650
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 ...
- 64.6k
29
votes
1
answer
46k
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 ...
- 64.6k
-1
votes
1
answer
595
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 ...
- 3,327
4
votes
1
answer
753
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) );...
- 64.6k
28
votes
2
answers
23k
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 ...
- 64.6k
1
vote
1
answer
2k
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?
- 113
1
vote
1
answer
2k
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 ...
- 301
0
votes
2
answers
887
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 = ...
- 101
17
votes
1
answer
24k
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`.`...
- 663
11
votes
2
answers
10k
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 ...
- 347