I have two tables with different schemas:
Base A, table T1:
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`uid` int(11) NOT NULL DEFAULT '0',
`language` varchar(12) NOT NULL DEFAULT ''
Base B, table T2:
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Type` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
`UserID` int(11) NOT NULL,
`Name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
I need to transfer data from T1 to T2 in this way:
A.T1.id -> B.T2.ID
A.T1.title -> B.T2.Name
A.T1.uid -> B.T2.UserID
As you can see fields A.T1.language
and B.T2.Type
are not needed.
I think I should do this migration through dump of CSV. But this is all I have come up to.
Any idea?
UPDATE
Thank you guys for your answers. Please forgive me for not being clear enough, I should have emphasized that my tables are in different bases, and even on different servers. So it is not as easy as to just insert fields from one table into another.
You can do it with a combination of UPDATE
and SELECT
query. However since TABLE 1
has the column title
which is of type VARCHAR(255)
and TABLE 2
has the column Name
which is of type VARCHAR(100)
might give a problem.
The following query can do this migration however any row with column title
having length more than 100 will be SHORTENED to 100.
INSERT INTO T2
(ID, Name, UserID)
SELECT id, SUBSTR(title, 0, 100), uid
FROM T1