Real Software Forums
http://forums.realsoftware.com/

How to create a copy of a table, but with a different name?
http://forums.realsoftware.com/viewtopic.php?f=3&t=39707
Page 1 of 1

Author:  tseyfarth [ Wed Jul 06, 2011 1:04 am ]
Post subject:  How to create a copy of a table, but with a different name?

Hello,

I am using PostgreSQL. Using the pgAdmin III or another tool, how can I create a copy of a table, but with a different name? This table is to record transactions, but since there are many of them, I want to separate them into separate tables to make it faster for report creation and searching. But since the data required to save is pretty much identical, I thought I'd break it up into appropriate tables.

Ideas anyone?

Thanks
Tim

Author:  Akiland [ Wed Jul 06, 2011 6:11 am ]
Post subject:  Re: How to create a copy of a table, but with a different na

Maybe you could use this?
http://www.mydigitallife.info/easily-duplicate-copy-or-backup-tables-in-oracle-postgresql-db2-and-sqlite-with-create-table-as-sql/

Author:  DaveS [ Wed Jul 06, 2011 7:45 am ]
Post subject:  Re: How to create a copy of a table, but with a different na

CREATE TABLE table2 AS SELECT * FROM table1


how much more difficult do you need it to be?

Author:  tseyfarth [ Wed Jul 06, 2011 2:29 pm ]
Post subject:  Re: How to create a copy of a table, but with a different na

Hi guys and thank you for your responses!
Yea, how much harder could it possible be?! NOT!

That was way cool and way easy.
Thank you both again!
Tim

Author:  vfran [ Mon Nov 19, 2012 8:49 am ]
Post subject:  Re: How to create a copy of a table, but with a different na

Does
CREATE TABLE table2 AS SELECT * FROM table1
also copy data and each individual column type?

Author:  DaveS [ Mon Nov 19, 2012 9:15 am ]
Post subject:  Re: How to create a copy of a table, but with a different na

vfran wrote:
Does
CREATE TABLE table2 AS SELECT * FROM table1
also copy data and each individual column type?


YES

it makes an exact duplicate of TABLE1 include column names, datatypes and complete data content.
if TABLE1 has 14 columns and 1000 rows, then TABLE2 will have 14 columns and 1000 rows

The only thing it does NOT do is re-create any INDEXES that may exist for TABLE1

Author:  neonash7777 [ Mon Nov 19, 2012 10:51 am ]
Post subject:  Re: How to create a copy of a table, but with a different na

DaveS wrote:
The only thing it does NOT do is re-create any INDEXES that may exist for TABLE1


I'm assuming it also doesn't include triggers too, but I haven't tested it.

Author:  DaveS [ Mon Nov 19, 2012 11:02 am ]
Post subject:  Re: How to create a copy of a table, but with a different na

neonash7777 wrote:
DaveS wrote:
The only thing it does NOT do is re-create any INDEXES that may exist for TABLE1


I'm assuming it also doesn't include triggers too, but I haven't tested it.


That is true.... ONLY THE STRUCTURE AND DATA CONTENT

and if you ONLY want the structure and NOT the data

CREATE table2 AS SELECT * FROM table1 WHERE 1=0




Also... back to the OP..... "faster for report creation"???
a) how many records are you talking here? 10's of millions?
b) have you considered indexes?

Author:  vfran [ Fri Jan 18, 2013 12:44 pm ]
Post subject:  Re: How to create a copy of a table, but with a different na

What if I want to copy all data from row A to row B, but I want to change column 'name' so name is NOT identical in row A and row B, but the rest of the data remains identical.

Author:  DaveS [ Fri Jan 18, 2013 12:47 pm ]
Post subject:  Re: How to create a copy of a table, but with a different na

create table1 as
select
field1,field2,field3,
field4 as new_name
from table2


in this instance you MUST refer to EVERY field you want to keep, even if you are NOT changing its name

Author:  vfran [ Fri Jan 18, 2013 1:05 pm ]
Post subject:  Re: How to create a copy of a table, but with a different na

OK. But I have up to 120 fields.

Author:  DaveS [ Fri Jan 18, 2013 1:34 pm ]
Post subject:  Re: How to create a copy of a table, but with a different na

and you have to name all 120 fields.....

either that or create an exact duplicate and change column names using ALTER TABLE
IF the database you are using supports that feature (not all do)

Author:  markwalsh [ Fri Jan 18, 2013 3:13 pm ]
Post subject:  Re: How to create a copy of a table, but with a different na

You can get the table's FieldSchema to obtain the field list, and add each field name (checking for whichever field(s) you want to omit or change names) to your SQL statement. Then it shouldn't matter if you have 120 or 1200 fields.

Author:  vfran [ Sat Jan 19, 2013 4:39 am ]
Post subject:  Re: How to create a copy of a table, but with a different na

Thanks. Just thought there was a simpler way with pure sql.
I did a work around which migt actually be better for the user:

- Listboxes show user the data.
- When/if User wants to copy row with all data, he is asked to enter a name.
- If name entered doesn't exist then shown data is done as an Insertrecord which works fine.

Page 1 of 1 All times are UTC - 5 hours
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/