CODEWITHSUNDEEP
mysql
Vikas Sharma
Vikas Sharma

Reputation: 599

Copy some fields from one table to another in MySQL with or without data

i am searching for hours and not found a good answer of this ,can anybody help me out to do this in MySQL:

  1. How to copy both the structure and data of Table-1 into Table-2,
    Table-1 and Table-2are given below:-

  2. How to copy the structure of Table-1 into Table-2 without any data .

    [Table-1  Store_Information]
Store_Name  Sales   Txn_Date
Los Angeles 1500    Jan-05-1999
San Diego   250     Jan-07-1999
Los Angeles 300     Jan-08-1999
Boston      700     Jan-08-1999

[Table-2  Geography]
Region_Name Store_Name
East        Boston
East        New York
West        Los Angeles
West        San Diego

I also tried some examples found on other threads like 

SELECT column_name1,column_name2,..
INTO new_table
FROM table_name;

and 

INSERT INTO table2
column1, column2..
SELECT column1, column2, ..
FROM table1;

Both queries i found from these links:-
http://blog.ubiq.co/copy-data-into-new-table-mysql/
http://blog.ubiq.co/insert-data-from-one-table-into-another-mysql/
But nothing seems to work. First query does not work because MySQL does not support SELECT INTO. Well insert statement will work when there will be a column in table2. So the main issue is how to copy some columns from one table to another table. Just structure, not data.

I am using MySQL. Database is same.Both tables is in same database. Both tables already exists in database.

Please help me to do this.

Upvotes: 0

Views: 1426

Answers (1)

Barmar
Barmar

Reputation: 780724

To copy the structure without data you can use the LIKE option to CREATE TABLE:

CREATE TABLE table2 LIKE table1;

You can then copy all the data with:

INSERT INTO table2
SELECT * FROM table1

If you want to copy some columns along with their data, you can do:

CREATE TABLE table2
SELECT col1, col2, col3, ...
FROM table1

This will just copy the datatypes, it won't copy indexes and options like auto_increment.

To copy some columns without any data, you could use the above procedure, but a WHERE clause that selects no rows:

CREATE TABLE table2
SELECT col1, col2, col3, ...
FROM table1
WHERE 1 = 0

Upvotes: 3

Related Questions