YogeshChauhan . com

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 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

Jun 16 What are Stored Procedures for SQL Server? Jun 16 What are Class Constants in PHP? Jun 15 A short basic guide on states in React Jun 15 How to define constants in PHP? Jun 15 How to define visibility for a property in PHP? Jun 15 How to use @if and @else in SCSS?

You might also like these

Content Blocks in SCSS (Sass)SCSSHow to remove WordPress TinyMCE Editor buttons?WordPressWhat Does Host-Based Intrusion Detection System (HIDS) Mean and What Are Some Advantages Over NIDS?MiscHow to get the full URL of current page in PHP?PHPSolution to “Call to undefined function mysql_error()” in RevSlider WordPress PluginWordPress@forward modules with a prefix in SCSS (Sass)SCSS