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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #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
You might also like these
How to Sort (Shuffle) an Array in Random Order in JavaScript?JavaScriptHow to Secure Web or Mobile Browsers?MiscellaneousHow to create a link tag button using CSS?CSSThe unique operator($) in Envision BasicEnvision BasicArguments in @mixin rules in SCSS (Sass)SCSSRecursion in JavaScriptJavaScript