Home » SQL & PL/SQL » SQL & PL/SQL » copy table structure
copy table structure [message #661350] Wed, 15 March 2017 11:46 Go to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I need to create a copy of table in same database. what is the best way to do this without creating manually table ?
copy means same columns with same data types.
Re: copy table structure [message #661351 is a reply to message #661350] Wed, 15 March 2017 11:48 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

- CREATE TABLE ... AS SELECT
- expdp/impdp
- dbms_metadata.get_ddl

Re: copy table structure [message #661353 is a reply to message #661351] Wed, 15 March 2017 11:59 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
i don't need to copy data, i just need the structure
Re: copy table structure [message #661354 is a reply to message #661353] Wed, 15 March 2017 12:04 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
asliyanage wrote on Wed, 15 March 2017 09:59
i don't need to copy data, i just need the structure
Nobody here prevents you from doing as you desire.
Please proceed without delay.
Re: copy table structure [message #661355 is a reply to message #661353] Wed, 15 March 2017 12:25 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
asliyanage wrote on Wed, 15 March 2017 17:59
i don't need to copy data, i just need the structure
Yes, do it in one the ways I said, there may be others.

Re: copy table structure [message #661356 is a reply to message #661355] Wed, 15 March 2017 16:04 Go to previous messageGo to next message
cookiemonster
Messages: 12989
Registered: September 2008
Location: Rainy Manchester
Senior Member
For CTAS you just add WHERE 1=2
Re: copy table structure [message #661357 is a reply to message #661356] Wed, 15 March 2017 16:19 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
cookiemonster wrote on Wed, 15 March 2017 14:04
For CTAS you just add WHERE 1=2
or
WHERE 0 = 1
Re: copy table structure [message #661358 is a reply to message #661357] Wed, 15 March 2017 21:24 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
thanks all
Re: copy table structure [message #661363 is a reply to message #661358] Thu, 16 March 2017 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So what is the conclusion?
Which way did you choose?
Why?

Re: copy table structure [message #661396 is a reply to message #661363] Thu, 16 March 2017 11:55 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
I used below

create table employees_copy as select * from employees where 1=0;

this will not copy any rows to new table
Re: copy table structure [message #661398 is a reply to message #661396] Thu, 16 March 2017 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes but you have neither the constraints nor the indexes on the table.

Re: copy table structure [message #661412 is a reply to message #661398] Thu, 16 March 2017 19:20 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
you mean this will not copy constraints and indexes, then what is the solution?
Re: copy table structure [message #661413 is a reply to message #661412] Thu, 16 March 2017 19:23 Go to previous messageGo to next message
BlackSwan
Messages: 25793
Registered: January 2009
Location: SoCal
Senior Member
- expdp/impdp
- dbms_metadata.get_ddl
Re: copy table structure [message #661420 is a reply to message #661412] Fri, 17 March 2017 02:29 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
asliyanage wrote on Fri, 17 March 2017 01:20
you mean this will not copy constraints and indexes, then what is the solution?
To read more than the first line of our posts.

Re: copy table structure [message #661441 is a reply to message #661420] Fri, 17 March 2017 12:16 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
ok michel. tnx
Re: copy table structure [message #661445 is a reply to message #661441] Fri, 17 March 2017 13:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Did you succeed to do it with the other methods?

Re: copy table structure [message #661519 is a reply to message #661445] Wed, 22 March 2017 08:27 Go to previous messageGo to next message
asliyanage
Messages: 60
Registered: January 2017
Member
other methods mean what methods? i tried only this method
Re: copy table structure [message #661523 is a reply to message #661519] Wed, 22 March 2017 09:08 Go to previous message
cookiemonster
Messages: 12989
Registered: September 2008
Location: Rainy Manchester
Senior Member
The first reply in this thread, from Michel, lists three methods. The two you skipped were repeated further down.
Pay attention to what others write.
Previous Topic: Conditional Join Logic
Next Topic: Orcale: Missing Group By expression working
Goto Forum:
  


Current Time: Wed Jan 17 23:12:28 CST 2018

Total time taken to generate the page: 0.07104 seconds