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

Posted on 2001-06-30
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.

Question by:javaq092999
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

Expert Comment

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

     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;

     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 || ');';

end CreateAsSelect;

Expert Comment

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.

Expert Comment

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:
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.


Author Comment

ID: 6244283
See dbalaski comments...

Accepted Solution

Vidyadhars earned 50 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;


Expert Comment

ID: 6246319
You could also do something like this in a higher-level GUI tool.  For example, in TOAD (free demo at ), 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.

Expert Comment

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.
Please click the Help Desk link on the left for Member Guidelines, Member Agreement and the Question/Answer process.

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.

To view your locked questions, please click the following link(s) and evaluate the proposed answer.

------------>  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

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.
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.

Author Comment

ID: 6881856

Expert Comment

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.

Moondancer - EE Moderator

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

705 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