Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    27

    Unanswered: select into statement

    Hi,

    Anyone know how to write the query below mssql (t-sql) in oracle (pl-sql)?

    t-sql:
    select * into newtable from existingtable

    Thanks.
    Warm Regards,
    Jacky

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    If the table does not exist yet:
    create table newtable as select * from existingtable

    If the table does exist
    insert into newtable select * from existingtable
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Additionally, the second solution might cause problems if "newtable" and "existingtable" aren't the same regarding column datatypes. In such a case, you should use column list in order to successfully insert data into another table. Something like this:
    Code:
    SQL> create table existingtable (a number(2), b varchar2(10));
    
    Table created.
    
    SQL> create table newtable (c varchar2(10), d number(2));
    
    Table created.
    
    SQL> insert into existingtable values (2, 'first');
    
    1 row created.
    
    SQL> insert into newtable select * from existingtable;
    insert into newtable select * from existingtable
                                *
    ERROR at line 1:
    ORA-01722: invalid number
    
    
    SQL> insert into newtable (c, d) (select b, a from existingtable);
    
    1 row created.
    
    SQL>

Posting Permissions

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