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

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:
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.


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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 47
Oracle DBLINKS From 11g to 8i 3 47
oracle forms question 22 40
subtr returning incorrect value 8 26
Introduction A previously published article on Experts Exchange ("Joins in Oracle", makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 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