hi all

Can i use Select Into statement in Oracle for creating a table?

Thnx

Ashu

Ashu,
Your query is not clear.
Do you want to insert the selected values into a table,if so you can use
Insert into destination_table (col_one,col_two,col_three) values(select col_one,col_two,col_three from source_table)

Regards,
Ram.

hi Ram

actually i wanna do 2 things,
1) Create Table with same structure and
2) Insert all the rows of frst table into the new table


thnx
Ashu

Ashu,
Create Source table:
create table source_table(colone number(9),coltwo varchar2(9));
Insert some data into the source_table:
   Insert into source_table values(1,A)
   Insert into source_table values(2,B)
   Insert into source_table values(3,C)
   Insert into source_table values(4,D)
Create Destination Table:
Create table Destination_Table(colone number(9),coltwo varchar2(9));
Inserting Source table data into Destination table:
   Insert into Destination_Table values(select colone,coltwo from source_table)

Hope it will helps you.

Regards,
Ram.

Hi Ram

i also used the same method
but isn't is a way around

wht i m asking is can we create and populate a table as we can do in SQL Server

thnx

Ashu

hi ashu!!
try the following statement
create table <t1> as select * from (t2>
t1 table'll create same structure and data as t2

You can also use the 'COPY' command from SQL Plus. Very powerful. Here is what the help file says about the 'COPY' command:

usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
  <db> : database string, e.g., scott/tiger@d:chicago-mktg
  <opt> : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
  <table>: name of the destination table
  <cols> : a comma-separated list of destination column aliases
  <sel> : any valid SQL SELECT statement

to create a new table with the same structure and data of old table use this statement
CREATE TABLE New_Table AS
SELECT * FROM Old_Table;

Hi,
  You can use the same structure for Creating table. There is no problem.

Thanx
Adalarasu