?
Solved

CREATE TABLE newTable as (SELECT * FROM oldTable WHERE 1=2)

Posted on 2001-06-30
9
Medium Priority
?
2,018 Views
Last Modified: 2012-08-13
I have number of tables and I want to make same tables again with different names without keeping the data, ie,  copy their structures only. I am using "CREATE TABLE newTable as (SELECT * FROM oldTable WHERE 1=2)". Everyting is fine but the newTable created keeps at least the NOT NULL constraints while throwing out all other type of constraints. My questions are :

Q1. How to modify the above statement so that even NOT NULL constraints are thrown out.

Q2. Just opposite of what I asked above. How to modify the above statement so that ALL (yes all) constraints are intact in the newTable.

Regards
javaq
0
Comment
Question by:javaq092999
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 2

Expert Comment

by:dbaora
ID: 6242276
Hi,
you can user the below procedure to create a table without any contsraint:
create or replace procedure CreateAsSelect (tab_name in varchar2)
is

     string     varchar2(1000) := '';
     new_table     varchar2(50);
     col_name     varchar2(100) := '22';
     cursor c1 is select column_name || ' ' || data_type || '(' || data_length || ')'
     from user_tab_columns
     where table_name = tab_name order by column_id;

begin
     new_table := tab_name || '_TMP';
     string := 'create table ' || new_table || '(';

     open c1;
     while col_name is not null LOOP
          fetch c1 into col_name;
          exit when c1%NOTFOUND;
          string := string || col_name || ',';
     end LOOP;
     close c1;
     string := substr(string,1,length(string)-1);
     string := string || ');';

     dbms_output.put_line(string);
     
end CreateAsSelect;
/
0
 

Expert Comment

by:brown_ism
ID: 6243225
In addition of the above you can use the "ACCEPT" with variable if you do not know the table values initially.  

Good luck.
0
 
LVL 9

Expert Comment

by:dbalaski
ID: 6243575
brown_ism  --  you should have posted as a comment --  not as a PROPOSED ANSWER
your answers doesn't really provide more than what DBAORA has already suggested.
read the link: http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:javaq092999
ID: 6244283
See dbalaski comments...
0
 

Accepted Solution

by:
Vidyadhars earned 150 total points
ID: 6244805
you can use "copy from .." command. Syntax is:

Copy from username1/password1@connectstring1 to username2/password2@connectstring2 create newtable using-
select * from oldtable where 1=2;

Bye.
0
 
LVL 5

Expert Comment

by:ser6398
ID: 6246319
You could also do something like this in a higher-level GUI tool.  For example, in TOAD (free demo at www.toadsoft.com ), you go to Database / Schema Browser, then select the table you wish to copy, and click on the Create Script icon.  It will bring up a series of options to allow you to include/not include Indexes, Constraints, etc., then paste that script to the clipboard or to a file.  Just change the table name in that script and run it to create the copy table.
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6877684
ADMINISTRATION WILL BE CONTACTING YOU SHORTLY.  Moderators Computer101 or Netminder will return to finalize these if still open in seven days.  Please post closing recommendations before that time.

Question(s) below appears to have been abandoned. Your options are:
 
1. Accept a Comment As Answer (use the button next to the Expert's name).
2. Close the question if the information was not useful to you. You must tell the participants why you wish to do this, and allow for Expert response.
3. Ask Community Support to help split points between participating experts, or just comment here with details and we'll respond with the process.
4. Delete the question. Again, please comment to advise the other participants why you wish to do this.

For special handling needs, please post a zero point question in the link below and include the question QID/link(s) that it regards.
http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
 
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.  http://www.experts-exchange.com/jsp/cmtyHelpDesk.jsp

Please click you Member Profile to view your question history and keep them all current with updates as the collaboration effort continues, to track all your open and locked questions at this site.  If you are an EE Pro user, use the Power Search option to find them.  Anytime you have questions which are LOCKED with a Proposed Answer but does not serve your needs, please reject it and add comments as to why.  In addition, when you do grade the question, if the grade is less than an A, please add a comment as to why.  This helps all involved, as well as future persons who may access this item in the future to seek help.

To view your open questions, please click the following link(s) and keep them all current with updates.
http://www.experts-exchange.com/questions/Q.20023290.html
http://www.experts-exchange.com/questions/Q.20121869.html
http://www.experts-exchange.com/questions/Q.20126064.html
http://www.experts-exchange.com/questions/Q.20139990.html
http://www.experts-exchange.com/questions/Q.20143257.html
http://www.experts-exchange.com/questions/Q.20148988.html
http://www.experts-exchange.com/questions/Q.20150711.html
http://www.experts-exchange.com/questions/Q.20158263.html
http://www.experts-exchange.com/questions/Q.20163902.html
http://www.experts-exchange.com/questions/Q.20163903.html
http://www.experts-exchange.com/questions/Q.20181528.html
http://www.experts-exchange.com/questions/Q.20245208.html
http://www.experts-exchange.com/questions/Q.20245590.html
http://www.experts-exchange.com/questions/Q.20251382.html
http://www.experts-exchange.com/questions/Q.20253111.html


To view your locked questions, please click the following link(s) and evaluate the proposed answer.
http://www.experts-exchange.com/questions/Q.20143170.html
http://www.experts-exchange.com/questions/Q.20143993.html

PLEASE DO NOT AWARD THE POINTS TO ME.  
 
------------>  EXPERTS:  Please leave any comments regarding your closing recommendations if this item remains inactive another seven (7) days.  Also, if you are interested in the cleanup effort, please click this link http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20274643

Moderators will finalize this question if still open in 7 days, by either moving this to the PAQ (Previously Asked Questions) at zero points, deleting it or awarding expert(s) when recommendations are made, or an independent determination can be made.  Expert input is always appreciated to determine the fair outcome.
 
Thank you everyone.
 
Moondancer
Moderator @ Experts Exchange

P.S.  For any year 2000 questions, special attention is needed to ensure the first correct response is awarded, since they are not in the comment date order, but rather in Member ID order.
0
 

Author Comment

by:javaq092999
ID: 6881856
thanks
0
 
LVL 1

Expert Comment

by:Moondancer
ID: 6883231
Thank you for returning and finalizing this question.

Please take an extra moment when you grade questions less than an "A" to comment as to why.  This helps not only the experts who have helped you, but also others in the future who may access this question to get help.  If you've found alternative solutions to achieve your goal and add it here, it further adds value to this item which has not moved to our PAQ (Previously Asked Question database).  Anytime that you need our help, for example, if the grade was chosen in error, let us know by posting a zero point question in the Community Support with the URL to the question involved, we're happy to help.  The points to you are the same, regardless of grade assigned, but the expert points which flow are directly impacted by grades assigned.  There is more about this in the HELP DESK link regarding Questions and Answers.

Thanks,
Moondancer - EE Moderator
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question