How to Create a Copy of a Table in SQL and MySQL?
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;

Leave a Reply

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 How to set opacity or transparency using CSS? #3 Pagination in CSS with multiple examples #4 How to make HTML form interactive and using CSS? #5 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #6 How to uninstall Cocoapods from the Mac OS?

Recently Posted

Mar 3 How to embed YouTube or other video links in WordPress? Mar 3 How to change the Login Logo in WordPress? Mar 3 substring() Method in JavaScript Mar 3 Window setInterval() Method in JavaScript Mar 2 How to zoom an element on hover using CSS? Mar 2 the box-sizing property in CSS

You might also like these

Custom Post Type Template Files in WordPressWordPressModules and its Core features in JavaScriptJavaScriptQuick Introduction to 11 Array Iteration Methods in JavaScriptJavaScriptAttributes and Properties in JavaScript and HTMLHTMLIntroduction to Angular modules Part 2: NgModules and componentsAngularHow to get query string values in JavaScript? (URL Manipulation using URLSearchParams)JavaScript