CODEWITHSUNDEEP
sql
Christian
Christian

Reputation: 2282

CREATE TABLE ... AS SELECT with discrete values with explicit column names

I want to execute this statement

CREATE TABLE Tab2 AS SELECT 1, "abc", 123456789.12 UNION SELECT 2, "def", 25090003;

on an SQL database. However column names of the resulting table are 1, "abc", and 123456789.12. Is there a way to give the columns explicit names, e.g. like col1, col2, and col3?

I know, I could make it two statements, first a create table with explicit column names and then an INSERT INTO ... SELECT, but I wonder if theres a way to make it a single statement.

Upvotes: 0

Views: 304

Answers (3)

lc.
lc.

Reputation: 116458

You should be able to give the columns in your SELECT aliases using the AS keyword:

CREATE TABLE Tab2 AS 
SELECT 1 AS col1, \"abc\" AS col2, 123456789.12 AS col3 
UNION SELECT 2, \"def\", 25090003;

Upvotes: 1

paxdiablo
paxdiablo

Reputation: 881403

You can use the as clause for columns:

CREATE TABLE Tab2 AS
    SELECT 1           as col1,
    \"abc\"            as col2,
    :

Upvotes: 1

Klas Lindbäck
Klas Lindbäck

Reputation: 33273

Simply give the selected columns alias names as desired:

CREATE TABLE Tab2 AS SELECT 1 AS COLUMN1, \"abc\" AS COLUMN2, 123456789.12 AS COLUMN3 UNION SELECT 2, \"def\", 25090003;

Upvotes: 1

Related Questions