Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2002
    Posts
    42

    Unanswered: Create table with a SELECT statement?

    Hello everyone,

    I have a quick one here. How can I create a table using a SELECT statement? I can't find the right syntax...

    In Oracle I'd simply do something like
    CREATE TABLE table_name AS
    SELECT...

    Thanks...

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I am not sure about V9, but in version 8 you can use the create table like syntax, but the new table will have the same columns as the original.

    You can insert into with a select from, but that does not create the table.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There is no way to create a table based on the columns of a query. The "CREATE TABLE mytable_copy LIKE MyTable" syntax only works for tables, views, or nicknames that are in the catalog.

    Are you trying to create a table that has the same columns as the query, or are you trying to create a MQT?

    Andy

  4. #4
    Join Date
    Jul 2002
    Posts
    42
    I'm trying to create a table that has the same columns as the query, basically create a physical view.

    It is for testing purposes.

    I could always create the table and then do a INSERT INTO / SELECT, but the idea of using the output of a query is faster for temporary tables... too bad...

    I'm using v8 by the way.

    Thanks fellows!

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You could create a VIEW based on the query, then create the TABLE based on the VIEW:


    CREATE VIEW MyView as (SELECT ...)
    CREATE TABLE MyTable LIKE MyView

    HTH

    Andy

  6. #6
    Join Date
    Sep 2003
    Posts
    237
    Try
    CREATE TABLE Y AS (SELECT * FROM X ) WITH NO DATA;
    INSERT INTO Y SELECT * FROM X;
    mota

  7. #7
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    CREATE TABLE Y AS (SELECT * FROM X ) definition only
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •