Real Software Forums

The forum for Real Studio and other Real Software products.
[ REAL Software Website | Board Index ]
It is currently Wed Nov 14, 2018 11:04 pm
xojo

All times are UTC - 5 hours




Post new topic Reply to topic  [ 14 posts ] 
Author Message
 Post subject: How to create a copy of a table, but with a different name?
PostPosted: Wed Jul 06, 2011 1:04 am 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
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


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Wed Jul 06, 2011 6:11 am 
Offline
User avatar

Joined: Tue Jan 04, 2011 3:02 am
Posts: 1236
Location: Jönköping, Sweden
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/

_________________
Image http://www.linkedin.com/in/albinkiland
Dev. iMac 27" + 2x22" LG (2.8GHz Intel Core i7, 12GB RAM, 120GB SSD) OS X 10.8
Xojo Pro 2013r1


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Wed Jul 06, 2011 7:45 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
CREATE TABLE table2 AS SELECT * FROM table1


how much more difficult do you need it to be?

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Wed Jul 06, 2011 2:29 pm 
Offline

Joined: Sat Dec 04, 2010 9:14 pm
Posts: 918
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


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Mon Nov 19, 2012 8:49 am 
Offline

Joined: Mon Dec 07, 2009 7:30 pm
Posts: 121
Location: Europe/Italy
Does
CREATE TABLE table2 AS SELECT * FROM table1
also copy data and each individual column type?


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Mon Nov 19, 2012 9:15 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
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

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Mon Nov 19, 2012 10:51 am 
Offline
User avatar

Joined: Mon Nov 29, 2010 7:01 pm
Posts: 446
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Mon Nov 19, 2012 11:02 am 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
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?

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Fri Jan 18, 2013 12:44 pm 
Offline

Joined: Mon Dec 07, 2009 7:30 pm
Posts: 121
Location: Europe/Italy
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.


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Fri Jan 18, 2013 12:47 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
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

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Fri Jan 18, 2013 1:05 pm 
Offline

Joined: Mon Dec 07, 2009 7:30 pm
Posts: 121
Location: Europe/Italy
OK. But I have up to 120 fields.


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Fri Jan 18, 2013 1:34 pm 
Offline
User avatar

Joined: Sun Aug 05, 2007 10:46 am
Posts: 4931
Location: San Diego, CA
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)

_________________
Dave Sisemore
iMac I7[2012], OSX Mountain Lion 10.8.3 RB2012r2.1
Note : I am not interested in any solutions that involve custom Plug-ins of any kind


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Fri Jan 18, 2013 3:13 pm 
Offline

Joined: Wed May 20, 2009 4:43 pm
Posts: 958
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.

_________________
RB 2009r4 Windows XP


Top
 Profile  
Reply with quote  
 Post subject: Re: How to create a copy of a table, but with a different na
PostPosted: Sat Jan 19, 2013 4:39 am 
Offline

Joined: Mon Dec 07, 2009 7:30 pm
Posts: 121
Location: Europe/Italy
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.


Top
 Profile  
Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 14 posts ] 

All times are UTC - 5 hours


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group