MySQL

Hello,

I want to create a new table in a database that is a copy of an existing table.

I exported the existing table to my local machine. I noticed that all the insert statements (of course) have the name of the old table. I am using phpMySQL to manage the tables.

The name of the old (existing) table is resv. I want to create resvt that is a copy.

How do I do that?

It is VERY IMPORTANT that the current resv not be effected in  ANY way. It is a live table taking on-line reservations (thus the name) ALL the time.

Thanks
Richard KortsAsked:
Who is Participating?
 
lwadwellCommented:
If the new table will be in the same database ... why not do
    INSERT INTO resvt
    SELECT * FROM resv

after the new table is defined.
0
 
GaryCommented:
Ermmm open in notepad and do a replace on the name - or am I missing something.
0
 
Barry62Commented:
I assume you backed up the original table as an SQL file?

If so, open that file with Wordpad, not notepad.  If you open it with notepad, all of the line breaks are gone and it will be hard to to read.

Anyway, once it is open change the create statement to refer to resvt instead of resv.

Then change the insert statement to insert into resvt instead of resv.

Then import the file into MySQL and your done.  The original file will remain untouched.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Ray PaseurCommented:
you backed up the original table
Best advice ever!

You can rename the existing table then run the reload script.  There are a lot of ways to skin this cat.  The main thing is to avoid ever doing any operation on a live web site.  Set up a parallel test site.  It's what the pros would do.

And use some state-of-the-art test driven design.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_7830-A-Quick-Tour-of-Test-Driven-Development.html
0
 
Richard KortsAuthor Commented:
To all,

In the interest of caution (as most suggested), I created a BRAND new database. It has no tables. I am using phpMyAdmin

I clicked the SQL icon at the top of the left panel; I pointed it to the backed up SQL file of the one table & clicked "Go". It runs for several minutes, then stops. Nothing was imported.

I tried it 3 different times; same every time.

I'm suspicious that it may be that the backup file is too big to do it all in one fell swoop. The file is 32.6 MB.

Any suggestions?
0
 
GaryCommented:
Start by zipping the file.
Then if it is still above your upload limit you will need to edit the settings for
upload_max_filesize = ; //Size goes here

Is this shared hosting?
0
 
Richard KortsAuthor Commented:
To GaryC123,

zipping? Zip the SQL file I downloaded & upload the zipped version?

I can't see how that will work.

I'm trying now to break it into several pieces & upload the pieces one at a time.
0
 
GaryCommented:
Are you not getting any error messages?
It's either a script timeout or its timing out on the upload, or the file is too big (zipping will fix this).
0
 
Richard KortsAuthor Commented:
To GaryC123.

This just in as I read this post.

Trying to load a piece about 6 MB.

#2006 - MySQL server has gone away

Where do I update that parameter? php.ini? Or is it in phpMyAdmin somewhere?

Thanks
0
 
GaryCommented:
Are you on shared hosting?
0
 
Richard KortsAuthor Commented:
To GaryC123,

Yes, shared hosting.

Richard
0
 
GaryCommented:
You very probably cannot change any settings and may need to contact your host.
If they are in a good mood they will usually import the script for you.

PhpMyAdmin has some kind of option to break up sql statements into smaller blocks if I remember correctly (don't use it anymore)
So this maybe a better way to go rather then 1 enormous insert which is the reason for your errors - I just don't remember where the option is.
0
 
GaryCommented:
When exporting in custom mode you have the option
Syntax to use when inserting data

Use the last option (Example: INSERT INTO tbl_name VALUES (1,2,3))
So all your inserts are seperate querys instead of something like this.
INSERT INTO `cities` (`id`, `country`, `city_code`, `city_name`) VALUES
(2044, 'AD', '02', 'Canillo'),
(2045, 'AD', '03', 'Encamp'),
(2046, 'AD', '04', 'La Massana'),

Open in new window

0
 
Richard KortsAuthor Commented:
To GaryC123,

I'm not clear on the SQL Export options.

See attached.

What should I use?

Thanks
exportmySQL.JPG
0
 
GaryCommented:
Select Complete Inserts
0
 
Richard KortsAuthor Commented:
I guessed that & I'm doing it in pieces. LITTLE pieces.

This is a table of reservations for an Airport VAN service. Goes back to 2010. I got the first half of 2010 as one file. Had to break 2nd half into Q3 & Q4. Did Q1 for 2011. I don;t think all the Q's in 2011 will go as one file, I'm CERTAIN 2012 won't, probably have to make it months.

This is a royal pain.

There HAS to be an easier way.

Rkorts
0
 
GaryCommented:
Who's your host? Have you tried asking them?
In the future have a script you can run daily to automate the backups or if you can create a cron job.
0
 
Barry62Commented:
When you imported the SQL file into your new database, did it at least create the table?

If so, start with step 2 below:

Step 1.  Copy the create statement from the SQL file.  Go to the SQL tab of the NEW database.  Paste the create statement in the SQL area and run it.

Step 2.  Copy the Insert statement from the SQL file along with however many records you wish.  Paste them into the SQL area of the NEW table (REPLACING the create SQL).  Run it.

Step 3.  Copy the next number of rows (starting at the 'values' clause where you left off before) and paste them into the SQL area, replacing the previous value clauses.  Run it.

Continue this until all data is copied into the new table of the new database.
0
 
Richard KortsAuthor Commented:
Copying the resv table to a new database; a VERY tedious job.

Moving content to new table once that was done; as per lwadwell; instant & a piece of cake. Instant with over 100K records!!!
0
 
Barry62Commented:
OK, I don't get it.  You keep the thread going about the difficulty of copying SO many records, and we are all trying to come up with ways to get around that, and then you accept a solution that was proposed BEFORE the problems that YOU brought up.  Thanks for making many of us believe that they even had a CHANCE of getting their solution accepted.
0
 
Richard KortsAuthor Commented:
To Barry62,

I am sorry about how this evolved. I DID NOT want to try the solution recommended by lwadwell without first building a BRAND new database & loading the table, then I ran into the incredibly time wasting issue connected with the size of the table.

I am open to reopening & asking the moderator to change things.

What do you think is fair?
0
 
Barry62Commented:
No, that's fine.  I'll just get over it.  It's just that here lately, every time I offer a solution, no matter how absolutely correct it is, someone else's solution always gets selected.  Most of the time it's a matter of the author not explaining things clearly.  Not that you didn't, but I  am just frustrated.

Thanks for the offer, but let the decision stand as is.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.