Create Table AS in MySQL or MairaDB - MySQL Developer Tutorial

Create Table AS in MySQL : 

Create table As in MySQL or MariaDB is used to create copy of a table of you can insert the output of Select query into new table.

1) How to create copy of Entire table in MySQL or MariaDB:

if you need to make a copy of entire existing table, you can use below syntax.

create table NewTableName AS
Select * from ExistingTableName;


Example :

let's say if I have table customer and I want to create the copy of customer with name "customer_backup", I can use below statement in MySQL Database.



create table customer_backup AS
Select * from customer;



2) How to create table for output of Select Query for specific Columns

You can have few columns from table in your select query or your select query can have joins, group by etc. The output can be written to new table.



create table NewTableName AS
Select column1,Column2 from Table1 Inner join table2 on
table1.id=table2.id
group by column1,column2;



3) Add Columns beside Select Query Columns :

You can create extra columns at the same time when you are going to insert the data from your select query.  In below example, I am adding id column of int type and two columns will be added to customer table from select query.


create table customer_backup(id int) AS 
Select firstname, lastname from customer;



4) Defining Data Types for Columns :

You can define the data types of columns while creating the copy table or new table from select query output. In below example, I have defined the data type of customer_backup , also in Select query I have used the now( ) function to populate createdate column.


create table customer_backup
(createddate  datetime,firstname varchar(20),lastname varchar(20)) AS

Select now() as createddate,firstname,lastname from customer;



Watch the video to see different scenarios covered for "Create table AS".

1 comment: