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 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #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

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to create a simple digital clock using JavaScript?JavaScriptHow to create a pricing table using flex in CSS?CSSHow to make flexbox items of the same size?CSSAdd animation to your skills bar using CSSCSSJavaScript String Properties and MethodsJavaScriptWhat’s the difference between variables in CSS and SCSS (Sass)?CSS