Home » SQL & PL/SQL » SQL & PL/SQL » Create Table via SELECT statement, can I ? (Oracle 10g)
Create Table via SELECT statement, can I ? [message #286187] Thu, 06 December 2007 16:07 Go to next message
icm63
Messages: 22
Registered: December 2007
Junior Member
I am from MS SQL world. I could create a table via a SELECT statement like this...

MS SQL : SELECT <Field>,<Feild> INTO <DestinationTable> FROM <SourceTable>

What Can I do with PLSQL, can I do the same thing >
Re: Create Table via SELECT statement, can I ? [message #286190 is a reply to message #286187] Thu, 06 December 2007 16:38 Go to previous messageGo to next message
tcox
Messages: 2
Registered: December 2007
Location: Portland, OR
Junior Member
Yes, try this:

begin
execute immediate
'create table foo as select * from joe.foo' ;
end;

You have to have 'select' privileges on the table 'joe.foo'.
Re: Create Table via SELECT statement, can I ? [message #286192 is a reply to message #286187] Thu, 06 December 2007 16:42 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is a bad, bad, bad idea to be creating database objects using PL/SQL!

Object creation code should be done from SQL that is maintained under a source code control system.
Re: Create Table via SELECT statement, can I ? [message #286232 is a reply to message #286187] Thu, 06 December 2007 23:54 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
In SQL, (not PL/SQL):

CREATE TABLE <DestinationTable> AS SELECT <Field>, <Field> FROM <SourceTable>;
Re: Create Table via SELECT statement, can I ? [message #286264 is a reply to message #286232] Fri, 07 December 2007 01:11 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Although Barbara and Ana told/showed you how to do this in Oracle, I think a word of warning would be in place here.
In SQLServer/Sybase, often intermediate results from SQL are stored the way you describe. Then another (series of) SQL-query is fired against this new table
This is _not_ how we do it in Oracle. In Oracle we tend to stuff it all in one big SQL. Oracle is GOOD in joining and doing complex SQL.
Re: Create Table via SELECT statement, can I ? [message #286271 is a reply to message #286264] Fri, 07 December 2007 01:29 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Furthermore, regarding the fact that "MS SQL Server" rings a bell called "is it a temporary table you are dealing with?", perhaps you might be interested in the Global Temporary Tables feature Oracle offers:
Oracle documentation

In addition to permanent tables, Oracle can create temporary tables to hold session-private data that exists only for the duration of a transaction or session.

The CREATE GLOBAL TEMPORARY TABLE statement creates a temporary table that can be transaction-specific or session-specific. For transaction-specific temporary tables, data exists for the duration of the transaction. For session-specific temporary tables, data exists for the duration of the session. Data in a temporary table is private to the session. Each session can only see and modify its own data.
Re: Create Table via SELECT statement, can I ? [message #286511 is a reply to message #286187] Fri, 07 December 2007 12:49 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Also doing a CTAS (Create Table As Select) will only copy the structure and data into the new table. It will NOT put in any constraints except not null and will not recreate indexes.
Previous Topic: Running multiple scripts
Next Topic: Problem with Oracle Directories
Goto Forum:
  


Current Time: Mon Feb 17 19:10:16 CST 2025