Yogesh Chauhan's Blog

How to Create a Copy of a Table in SQL and MySQL?

in SQL/MySQL on January 10, 2020

Why do we need a copy of a table? Can't we just perform query on the table itself? Yes we can and that's helpful if you're only planning to use SELECT statement and just fetch data from the table. However, if you want to use INSERT, UPDATE or DELETE query statements, you should have a backup table in case something weird happens. We can use the copied table just for testing purposes as well. There are many other reasons you might want a copy of your table. It all depends on your role and responsibilities and privileges that you have.

Let's look at the different examples one by one to understand it better.

Warning: When you use the SELECT statement to create a copy of a table, only the columns and data are copied. Primary keys, foreign keys, default values and indexes won't get included in the copied table.

First of all, let's take a look at the syntax.

CREATE TABLE table_name AS
SELECT column_name(s)
FROM table_source
[WHERE search_condition]
[GROUP BY column_name(s)]
[HAVING condition]
[ORDER BY column(s)];

This following query will create a complete copy of invoices table:

CREATE TABLE invoice_copy AS SELECT * FROM invoices;

The query above copies all the columns and rows form the invoices table into a new table invoice_copy.

This following query will create a partial copy of invoices table:

CREATE TABLE larger_invoices AS SELECT * FROM invoices
WHERE invoice_total>credit_total;

The query above will only copy the invoices in which the sellers invoice total is higher than credit total.

This query will create a copy of a table with summary rows from invoices table.

CREATE TABLE seller_balances AS SELECT seller_id, SUM(invoice_total) AS sum_of_invoices FROM invoices
WHERE (invoice_total-payment_total-credit_total)<>0
GROUP BY seller_id"; 

You can always delete all the table copies using DELETE TABLE statement. For example,

DROP TABLE large_invoices;
DROP TABLE seller_balances;

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to add Read More Read Less Button using JavaScript? #3 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface #Jan 14 How to display a student’s individual transcript in Colleague? #Jan 11 How to install PuTTY on a MacOS? #Jan 8 How to Install Xcode Command Line Tools on MacOS?
You might also like these
CONCAT and CONCAT_WS Functions in PostgresPostgresWordPress: How to loop through ACF group fields?WordPressSolution to Could not cast value of type ‘NSTaggedPointerString’ to ‘NSNumber’SwiftHow to get query string values in JavaScript? (URL Manipulation using URLSearchParams)JavaScriptHow to pass arguments in SCSS function?SCSSHow to create a full screen loader using CSS and JavaScript?CSS