?
Solved

MySQL

Posted on 2012-08-21
22
Medium Priority
?
465 Views
Last Modified: 2012-08-22
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
0
Comment
Question by:Richard Korts
  • 8
  • 8
  • 4
  • +2
22 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 38318763
Ermmm open in notepad and do a replace on the name - or am I missing something.
0
 
LVL 25

Accepted Solution

by:
lwadwell earned 1000 total points
ID: 38318793
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
 
LVL 8

Expert Comment

by:Barry62
ID: 38318916
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 111

Expert Comment

by:Ray Paseur
ID: 38318992
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
 

Author Comment

by:Richard Korts
ID: 38321207
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
 
LVL 58

Expert Comment

by:Gary
ID: 38321245
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
 

Author Comment

by:Richard Korts
ID: 38321344
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
 
LVL 58

Expert Comment

by:Gary
ID: 38321386
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
 

Author Comment

by:Richard Korts
ID: 38321407
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
 
LVL 58

Expert Comment

by:Gary
ID: 38321421
Are you on shared hosting?
0
 

Author Comment

by:Richard Korts
ID: 38321437
To GaryC123,

Yes, shared hosting.

Richard
0
 
LVL 58

Expert Comment

by:Gary
ID: 38321469
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
 
LVL 58

Assisted Solution

by:Gary
Gary earned 1000 total points
ID: 38321523
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
 

Author Comment

by:Richard Korts
ID: 38321733
To GaryC123,

I'm not clear on the SQL Export options.

See attached.

What should I use?

Thanks
exportmySQL.JPG
0
 
LVL 58

Expert Comment

by:Gary
ID: 38322176
Select Complete Inserts
0
 

Author Comment

by:Richard Korts
ID: 38322315
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
 
LVL 58

Expert Comment

by:Gary
ID: 38322336
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
 
LVL 8

Expert Comment

by:Barry62
ID: 38322621
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
 

Author Closing Comment

by:Richard Korts
ID: 38322842
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
 
LVL 8

Expert Comment

by:Barry62
ID: 38322888
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
 

Author Comment

by:Richard Korts
ID: 38322971
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
 
LVL 8

Expert Comment

by:Barry62
ID: 38323012
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

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses
Course of the Month13 days, 11 hours left to enroll

755 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