SQL - Create table from Select + user defined columns and values

Question

Currently I have the following SELECT statement:

CREATE TABLE  TEST AS
SELECT ROW_ID,
            PROM_INTEG_ID,
            INTEGRATION_ID,
            BILL_ACCNT_ID,
            SERV_ACCT_ID,
            CFG_STATE_CD   
FROM PRODUCTS
WHERE PROD_ID = 'TestProduct'
AND STATUS_CD = 'Active';

However I have to add some additional columns which do not exist in the PRODUCTS table and define them with my own name .e.g HIERARCHY

I tried using the WITH operand in my SQL query but it keeps failing as the syntax is wrong.

    CREATE TABLE  TEST AS
SELECT ROW_ID,
            PROM_INTEG_ID,
            INTEGRATION_ID,
            BILL_ACCNT_ID,
            SERV_ACCT_ID,
            CFG_STATE_CD   
            WITH
                  PRODUCT_HEIRARCHY varchar2(30)  'Test123Value'
FROM PRODUCT
WHERE PROD_ID = 'TestProduct'
AND STATUS_CD = 'Active';

So in summary, I want to pull in columns from an existing table as well as defining some of my own.

Any help appreciated

Gordon Linoff · Accepted Answer · 2014-12-09 13:48:52Z

9

Just add the columns to the select:

CREATE TABLE TEST AS 
    SELECT ROW_ID, PROM_INTEG_ID, INTEGRATION_ID, BILL_ACCNT_ID, SERV_ACCT_ID, CFG_STATE_CD,
           CAST('Test123Value' AS VARCHAR2(30)) as PRODUCT_HIERARCHY
    FROM PRODUCTS
    WHERE PROD_ID = 'TestProduct' AND STATUS_CD = 'Active';

Note that the cast() is not necessary. But it is a good idea if you want the column to have a specific type.

answered Dec 9, 2014 at 13:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

tomaytotomato Over a year ago

Thanks for this, out of curiosity is the WITH operator for multiple table SELECTS?

Mr_and_Mrs_D Over a year ago

What if we want all columns from the original table ? SELECT *, CAST('Test123Value' AS VARCHAR2(30)) as PRODUCT_HIERARCHY does not seem to work (ORA-00923: FROM keyword not found where expected)

Mr_and_Mrs_D Over a year ago

Answer (I am new to oracle database) - we can't use the * alone we need the table name: stackoverflow.com/questions/9727018/…

Gordon Linoff Over a year ago

@Mr_and_Mrs_D . . . You would qualify the *: products.*.

Lalit Kumar B · Accepted Answer · 2014-12-09 14:12:27Z

2

Also using CTE i.e. WITH clause as known commonly, you could create table.

CREATE TABLE t
AS 
WITH data AS ( 
   SELECT...
) 
SELECT * 
FROM data

answered Dec 9, 2014 at 14:12

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

3 Comments

Mr_and_Mrs_D Over a year ago

Could you spell the query so it answers the question ?

Lalit Kumar B Over a year ago

@Mr_and_Mrs_D The OP has not posted the details of the rest of the columns and tables he want to add to the select. So, I assume he has to JOIN the other tables and refer them in the column list using the table alias. All that will go inside the WITH clause here.

Mr_and_Mrs_D Over a year ago

Cf. the answer by @Gordon

Collectives™ on Stack Overflow

SQL - Create table from Select + user defined columns and values

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related