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.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
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;
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.
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.
Business Accounts
Answer for Membership
by: Raynard7Posted on 2006-07-06 at 20:31:40ID: 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