Lecture 5: Creating A Copy Of A Table

SQL
1 
-- 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.