Copyright

How to Copy Data Between Tables in MySQL Databases

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: How to Copy MySQL Databases

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:04 MYSQL
  • 1:48 Ignore Clause
  • 2:09 Copying From One Table…
  • 2:38 Copy to Different Database
  • 3:50 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed
Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

This lesson covers a specific task in MySQL Databases: copying data between tables. The concept is explained and examples provided for copying data between tables and copying data to a table that doesn't yet exist.

MySQL

Databases can become huge and unwieldy very quickly. Demands on database administrators are great; not only do you have to provide a huge amount of data to customers, but you also need to maintain that data so that it's manageable.

Copying data between tables is useful to reduce the size of tables, organize data more efficiently, or create temporary copies of data for specific queries. For example, if you wanted to copy all data about Florida customers to a separate table for analysis, MySQL provides an efficient way to do this. This not only preserves the original data, but it keeps the source table data separate and secure.

You can copy data from one table to another table (even if that table doesn't yet exist). Data can also be copied from a table in one database to another database.

MySQL provides a powerful option for copying data from one table to another table (or many tables).

The basic command is known as INSERT ... SELECT.

A full layout of the syntax is shown below:

INSERT [IGNORE]
  [INTO] table_name
  [(column_name, ...) ]
SELECT ...
FROM table_name WHERE ...

Note that both tables must exist. You would need to use CREATE TABLE to create either or both of the tables; however, this lesson will cover a cleaner way of accomplishing this.

Before we get into some of the specifics, let's look at a straightforward example of the statement: We will copy shopping cart data from one table to another, but only where the customer location is Florida. This will help maintain the Florida customers separately.

INSERT INTO shopping_cart_Florida (customer_name, customer_State, orderID)
SELECT shopping_cart.orderID
FROM shopping_cart WHERE shopping_cart.customerState = 'FL';

It's important to note that all statements end in a semicolon. We're showing line breaks for readability, but MySQL only looks for the semicolon to know that it is done processing that command. In subsequent examples in this lesson, there will be multiple lines; note the semicolon that denotes the end of the command.

IGNORE Clause

The INSERT ... SELECT syntax provides an IGNORE option. This will ignore (not copy) rows of data that could possibly create a duplicate key. This is important because we would not want to create several rows in the target table that are duplicates. This creates a mess for querying and data integrity.

Thus, the previous code would look like:

INSERT IGNORE INTO shopping_cart_Florida (customer_name, customer_State, orderID)
SELECT shopping_cart.orderID
FROM shopping_cart WHERE shopping_cart.customerState = 'FL';

Copying from One Table to the Next

The previous example showed us how to copy from one table to another. There is a slight problem with that approach: we have to assume that the shopping_cart_Florida table already exists. In order to use the code, we would have needed to use the CREATE TABLE command. While it works just fine, there is an option to do all this work in one pass:

CREATE TABLE IF NOT EXISTS shopping_cart_Florida LIKE shopping_cart;

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create An Account
Support