# Lecture 5: Creating A Copy Of A Table

**URL:** <https://forum.codewithmosh.com/t/lecture-5-creating-a-copy-of-a-table/24640>\
**Category:** SQL\
**Created:** [January 17, 2024, 3:57pm UTC](https://forum.codewithmosh.com/t/lecture-5-creating-a-copy-of-a-table/24640 "2024-01-17T15:57:41Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Adiv](https://sea2.discourse-cdn.com/flex020/user_avatar/forum.codewithmosh.com/adiv/32/7596_2.png) [@Adiv](https://forum.codewithmosh.com/u/Adiv)\
**Post date:** [January 17, 2024, 3:57pm UTC](https://forum.codewithmosh.com/t/lecture-5-creating-a-copy-of-a-table/24640/1 "2024-01-17T15:57:41Z")

</div>

```auto
-- In the sql_invoicing database, copy selected records from
-- invoices table into (not yet created) invoices_archived
-- table. Include client name and only invoices that have been paid.
-- In lecture, instructor used CREATE TABLE with a SELECT
-- subquery to create the backup table and then used TRUNCATE 
-- to empty it. 
-- I think the same can be accomplished with a SELECT .. INTO ???
-- Also, drop backup table if it already exists.

USE sql_invoicing;

-- The following query doesn't work in MySQL but should work in
-- T-SQL. So, in MySQL we have to use the CREATE TABLE syntax instead.
/*
DROP TABLE IF EXISTS invoices_archived;

SELECT 
    inv.invoice_id,
    inv.number,
    c.name AS client,
    inv.invoice_total,
    inv.payment_total,
    inv.invoice_date,
    inv.due_date,
    inv.payment_date
    
INTO invoices_archived
FROM invoices inv LEFT JOIN clients c USING(client_id)
WHERE
	inv.payment_date IS NOT NULL
*/

-- In MySQL, we need to use the CREATE TABLE statement in order to make
-- a copy of the data in a table, which is **NOT** the same as making a copy
-- of (duplicating) a table. 

DROP TABLE IF EXISTS invoices_archived;

CREATE TABLE invoices_archived AS
SELECT 
    inv.invoice_id,
    inv.number,
    c.name AS client,
    inv.invoice_total,
    inv.payment_total,
    inv.invoice_date,
    inv.due_date,
    inv.payment_date
    
FROM invoices inv LEFT JOIN clients c USING(client_id)
WHERE
	inv.payment_date IS NOT NULL

-- The above query can be executed repeatedly without error, since
-- the invoices_archived table will be dropped before an attempt is 
-- made to (re)create it.    

-- To create a new table *with the same structure*
-- *and properties* (e.g. indexes, constraints, etc.) as an existing table,
-- use **CREATE TABLE** new_table **LIKE** existing_table followed by 
-- **INSERT INTO** new_table **SELECT** * **FROM** existing_table.

```

I noticed that when constructing SELECT statements, the instructor frequently switches to a window with the relevant table structure in design mode, so he can see the column names. It is _much_ easier to simply drag column names from the Schemas window directly into the query window at the desired location in the code. This saves a lot of time and eliminates misspellings.

Thank you kindly.