Select Insert Query by mentioning all columns across databases

Question

I am working on redesigning of a legacy db and I have set new names to columns of old db. So, for instance, if olddb.oldtable under dbold has column descr, I have set it as description in new newdb.netable for column.

How can I mention individual columns in my query?

I am using MYSQL

Update: Both Databases are on different IP Addresses and I am using Navicat to transfer data.

What do you mean by 'mention'? I hope you kept track of all those changes. — Lorenz Meyer, Nov 16 '15 at 11:49
Any luck with this? If you find a solution please post it because I am interested in it too. — Kostas Mitsarakis, Nov 16 '15 at 14:21

Kostas Mitsarakis · Answer 1 · 2015-11-16T12:07:44.663

0

You can insert all columns at once without the need to mention the names using this:

INSERT INTO newtable (SELECT * FROM oldtable);

It will make an 1x1 match independently of column names.

If types don't match then will insert default values (not checked for all the type combination).

Note that column number must be the same on both tables otherwise an error like this will occur:

 #1136 - Column count doesn't match value count at row 1

edited Nov 16 '15 at 12:07

answered Nov 16 '15 at 11:46

Kostas Mitsarakis

4,772
3
23
37

1

Do remember column names are different in both db tables – Volatil3 Nov 16 '15 at 11:53
1

speaking of type matches, if a column is `int` and other is `tinyint`, it will not work? – Volatil3 Nov 16 '15 at 12:01
1

It will work but default value will be 0. It will work even if one column is timestamp and the other is int (but I haven't checked for all the combinations). – Kostas Mitsarakis Nov 16 '15 at 12:02
I am getting error `Error : Table 'olddb.table' doesn't exist`. I am trying this in my Db Client(Navicat pro) – Volatil3 Nov 16 '15 at 12:29
Hm, 'table' is a reserved word for MySQL. Try with backticks. – Kostas Mitsarakis Nov 16 '15 at 12:31
Well , real query is: `INSERT INTO homestead.categories (SELECT * FROM spec.cs_cats);` – Volatil3 Nov 16 '15 at 12:32
I just checked it using MySQL 5 using 2 different tables from 2 different databases and it works normally. Check the table names. – Kostas Mitsarakis Nov 16 '15 at 12:37
By default any Db client selects a single connection. I am mentioning db name as `spec `.categories, where `spec` is Db name. What am I doing wrong? my DB client is not recognizing old Db – Volatil3 Nov 16 '15 at 13:18
Both Databases are on different IP Addresses and I am using Navicat to transfer data. – Volatil3 Nov 16 '15 at 13:22
Oh, that explains it. I suggest you to make a new question for this specific problem. – Kostas Mitsarakis Nov 16 '15 at 13:23
I think that somehow you have to use different connection names. – Kostas Mitsarakis Nov 16 '15 at 13:34
I don't know if something like this exists but try: INSERT INTO server1_connection.homestead.categories (SELECT * FROM server2_connection.spec.cs_cats); – Kostas Mitsarakis Nov 16 '15 at 13:41
May be you find these useful: http://stackoverflow.com/questions/508100/mysql-select-from-another-server, http://dev.mysql.com/doc/refman/5.0/en/federated-use.html, http://archive09.linux.com/feature/52390 – Kostas Mitsarakis Nov 16 '15 at 13:52

score 0 · Answer 2 · answered Nov 16 '15 at 11:48

You can try like this:

INSERT INTO newtable (col1, col2, ..., )
SELECT col1, col2, ..., FROM oldtable

By trying the above query you can insert the specific column. So for example if your newtable has a column as description and old table as descr then you can mention it like:

INSERT INTO newtable (col1, col2, `description`,  ..., )
SELECT col1, col2, `descr` ,..., FROM oldtable

Also if the table column list is large and you want to copy all the columns and its data then you can simply use the wildcard charater * as:

INSERT INTO newtable
SELECT * FROM oldtable;

Select Insert Query by mentioning all columns across databases

2 Answers2