Home » SQL & PL/SQL » SQL & PL/SQL » two table creation
two table creation [message #214054] Sun, 14 January 2007 03:35 Go to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi All,

I requite to create same structure tables in many times due to my business requirement. There is any way I can create many table in one shot. I mean


create table demo1,demo2 as select * from demo

I know this syntax it wrong …any buddy can suggest the way which reduce my cumbersome work..


Thanks,
Yash
Re: two table creation [message #214055 is a reply to message #214054] Sun, 14 January 2007 03:58 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
If you would create a sql-script to execute, it is quite easy to copy-paste a line like
create table demo1 as select * from demo;
multiple times and edit the new tablename as required.
Save the file and execute it from sqlplus.
Re: two table creation [message #214056 is a reply to message #214054] Sun, 14 January 2007 04:04 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Sir,

it would be great if i can do the same through the command ...if it is not possible then ok.

--Yash
Re: two table creation [message #214071 is a reply to message #214056] Sun, 14 January 2007 15:07 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Saying that you need to create tables with the same structure as some other table, and giving an example of

CREATE TABLE demo1 AS SELECT * FROM demo;

it creates a table which looks like the original one, along with all data stored in it.

Now, back to that "structure" word. If a new table needs to be empty, you'd have to delete (or truncate) it after it is created. To avoid this second, unnecessary step, modify the original CREATE TABLE statement: add a WHERE clause which would prevent inserting data, for example

... WHERE 1 = 2;

This would create exactly the same table as the original one, but empty.
Re: two table creation [message #214113 is a reply to message #214054] Mon, 15 January 2007 00:49 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Thx Littlefoot,

My original question is that can I create two or more then two tables in one shot, Mean single create statement can create more then one tables?

Create table demo1, demo2, demo3 as select * from demo where 1=2;

Thanks,
Yash
Re: two table creation [message #214137 is a reply to message #214113] Mon, 15 January 2007 02:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it from pl/Sql:
BEGIN
  FOR idx IN 1..10 LOOP
    EXECUTE IMMEDIATE 'CREATE TABLE demo_'||idx||' AS SELECT * FROM demo WHERE 1=0';

  END LOOP;
END;
Re: two table creation [message #214138 is a reply to message #214137] Mon, 15 January 2007 02:05 Go to previous messageGo to next message
Littlefoot
Messages: 20897
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I know what was your question; but, it isn't possible through a single commnand. You could, though, create a procedure which would accept 3 parameters: name of the original table, and names of two new tables. Something like this:
CREATE OR REPLACE PROCEDURE prc_cr2tab
  ( par_orig  IN CHAR,
    par_dup_1 IN CHAR,
    par_dup_2 IN CHAR
  )
IS
BEGIN
  EXECUTE IMMEDIATE 'create table ' || par_dup_1 || 
                    ' as select * from ' || par_orig ||
                    ' where 1 = 2';

  EXECUTE IMMEDIATE 'create table ' || par_dup_2 || 
                    ' as select * from ' || par_orig ||
                    ' where 1 = 2';
END;
/
Re: two table creation [message #214142 is a reply to message #214054] Mon, 15 January 2007 02:14 Go to previous messageGo to next message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Ok Thx,


I can undesrtand it is not possible in single command ...



--Yash

Re: two table creation [message #214143 is a reply to message #214138] Mon, 15 January 2007 02:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think this whole issue is a non-issue. How often would one have a need to copy one table to multiple other tables?
Creating and following this thread was probably more effort than just typing it by hand.
Re: two table creation [message #214297 is a reply to message #214054] Mon, 15 January 2007 13:33 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Also, what possible business requirement would require that you have multiple tables exactly alike. If you are setting up a table per external client (for example), you would use multiple schema's of fine grain access control. As a programmer of 30 years, I have never run into a business situation where I had to make up a large number of tables that were all alike.
Re: two table creation [message #214369 is a reply to message #214054] Tue, 16 January 2007 01:25 Go to previous message
ammishra
Messages: 179
Registered: January 2007
Location: india
Senior Member
Hi Bill B,

I respect your 30 years experience. I am working for big client. They have lot of business unit each business unit have there own employees own project which they are working but business logic differ from business unit to business units …for Ex..Employee table need to contained same kind of record in for all the employees..U can say like that…why you are not merging all the employee in a single tables my answer is it is already existing functionality client do not want to use single table ….we don’t to use single table in all the business application if table require changes then we have to made changes in all the application where table is used..u can say like why are you not making single application. My answer is it is very hard to do merge 100 different business unit logic in a single application..There were lots of chances to error ..and any business unit can change here business logic …I am asking this question bcz I am not a oracle developer and I have just 2 year experience …I want to just confirm my thought with you people which have 30 + year experience


Thanks
Yash
Previous Topic: %rowtype and views
Next Topic: how to refresh a materialzed view which refreshes on demand
Goto Forum:
  


Current Time: Wed Dec 07 08:38:04 CST 2016

Total time taken to generate the page: 0.14355 seconds