Solved

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

Posted on 2001-06-30
9
1,952 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
9 Comments
 
LVL 2

Expert Comment

by:dbaora
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:javaq092999
Comment Utility
See dbalaski comments...
0
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.

 

Accepted Solution

by:
Vidyadhars earned 50 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0
 
LVL 1

Expert Comment

by:Moondancer
Comment Utility
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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Oracle 12c 10 99
oracle query help 18 74
How to return an OUT parameter from and ORACLE 3 34
sql query 9 18
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…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now