Solved

copy data and structure to new table

Posted on 2006-07-06
20
1,659 Views
Last Modified: 2008-01-09
I have tried these sql statements and its just not working.I am trying to create a table and keep the same data and structure as table1.

I have tried

create table table1 like table2<<<Keeps the same structure but no data

insert into table1(select * from table2) <<<< Keeps crashing the new table

and then I tried create table table1(select * from table2)<<< retrieves the data but not the structure such as primary keys etc.
0
Comment
Question by:thenone
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 4
  • 2
  • +2
20 Comments
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 200 total points
ID: 17055983
I have just tried this with some simple tables;

create table `test`.`TableName1` (    `a` int (10)   NULL ,  `b` int (19)   NULL ,  `c` int (19)   NULL    );
insert into `test`.`tablename1` ( `a`, `b`, `c` ) values (  '1',  '2',  '3' );
insert into `test`.`tablename1` ( `a`, `b`, `c` ) values (  '4',  '3',  '2' );
create table tablename2 like tablename1 ;
insert into tablename2   (select * from tablename1) ;

and it worked perfectly; you may need to check your syntax and try again - because this is how it should work
0
 
LVL 33

Assisted Solution

by:sajuks
sajuks earned 100 total points
ID: 17055996
you cannot have the indexes,pks and attributes like auto_increment, NOT NULL  wont be copied into the new table if u just use
create table tablename select  * from tablename2

you will have to do it in two steps
first create the table structure and then insert the data ...
CREATE TABLE table1  LIKE table2;
INSERT INTOtable1  SELECT * FROM 2;

0
 
LVL 8

Author Comment

by:thenone
ID: 17055998
so how would my sql statement be to create a table with the same data and structure.


by the way the table does have full text enabled
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 8

Author Comment

by:thenone
ID: 17056004
CREATE TABLE table1  LIKE table2;
INSERT INTOtable1  SELECT * FROM 2;

I have tried this but I think its because I have full text indexes it will not work.
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 17056005
It sounds like maybe you have a lot of data in table1?

An alternate way to do this is to use mysqldump to dump the table structure and contents of table1, then rename table1 to table2, then reload table1 from the .SQL file created by mysqldump.
0
 
LVL 8

Author Comment

by:thenone
ID: 17056006
I do have a lot of data hello Todd how is it going? I am trying to make this automated through a client application.
0
 
LVL 30

Accepted Solution

by:
todd_farmer earned 200 total points
ID: 17056011
Try this:

CREATE TABLE table1  LIKE table2;
ALTER TABLE table1 DISABLE KEYS;
INSERT INTO table1  SELECT * FROM 2;
ALTER TABLE table1 ENABLE KEYS;
0
 
LVL 8

Author Comment

by:thenone
ID: 17056013
ok will try one sec
0
 
LVL 8

Author Comment

by:thenone
ID: 17056041
it crashed the table
0
 
LVL 8

Author Comment

by:thenone
ID: 17056043
everything copied over crashed when I enabled keys
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 17056053
Can you describe what error messages (if any) you received when it crashed?  Are you talking about the server or the app?
0
 
LVL 8

Author Comment

by:thenone
ID: 17056059
I do not recieve any error messages except when I execute the query it tels me how many rows are effected and then I check the connections on the server and it sais status for the query repair.
0
 
LVL 8

Author Comment

by:thenone
ID: 17056082
when I tried this before I think it was comlaining about the primary key numbers they are auto numbers but it should work.
0
 
LVL 30

Expert Comment

by:todd_farmer
ID: 17056104
OK, so it seems like you have a new table - with content - but no keys.  Is that correct?  What happens when you try to run the ALTER TABLE table1 ENABLE KEYS statement in the mysql command-line client (or phpMyAdmin or whatever you use)?  I'm wondering if we can get more information about the root cause of the problem from another client.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17056108
in your second table make the column that is auto increment just a normal int column - and remove the primary key,

copy everything across;

then
add the auto_increment and the primary key

then run

update table table1 set auto_increment = (select max(keyID)  +1 from table 1)

0
 
LVL 8

Author Comment

by:thenone
ID: 17056122
couldn't i remove the column with the primary key and then just add it after i copy all of the data if so what is the alter command.
0
 
LVL 8

Author Comment

by:thenone
ID: 17056156
ok I think I have it Once I disable the keys and insert the data everything is fine it works beautiful it doesn't seem like I have to enable the keys.
0
 
LVL 8

Author Comment

by:thenone
ID: 17056362
ok I have to enable keys I do not know what I was thinking and when I do that the table crashes.
0
 
LVL 22

Expert Comment

by:NovaDenizen
ID: 17058889
What version of mysql do you have, thenone?  "SHOW VARIABLES LIKE 'version';"
0
 
LVL 8

Author Comment

by:thenone
ID: 17060261
I am using mysql version 5.20 which I believe is the latest one.I also believe that I ahve resoved my problem not unless someone has a better solution.

create table table1 like table2
alter table table1 disable keys
insert into table1 select * from table2
alter table table1 enable keys


I have aslo eliminated my auto increment primary keys which I didn't really need and this seemed to help.This process still takes alot of time though.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

724 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