I am using MySQL. I have a table called EMP, and now I need create one more table (EMP_TWO) with same schema, same columns, and same constraints. How can I do this?
Answer: To do this, the SQL CREATE TABLE syntax is: CREATE TABLE new_table AS (SELECT * FROM old_table WHERE 1=2); For example: CREATE TABLE suppliers AS (SELECT * FROM companies WHERE 1=2);
Example - Equality Operator In MySQL, you can use the = operator to test for equality in a query. The = operator can only test equality with values that are not NULL. For example: SELECT * FROM contacts WHERE last_name = 'Johnson';
To create a new table based on another tables structure / constraints use :
CREATE TABLE new_table LIKE old_table;
To copy the data across, if required, use
INSERT INTO new_table SELECT * FROM old_table;
Create table docs
Beware of the notes on the LIKE option :
Use LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table:
CREATE TABLE new_table LIKE original_table; The copy is created using the same version of the table storage format as the original table. The SELECT privilege is required on the original table.
LIKE works only for base tables, not for views.
CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With