Inserting data from different mysql databases in to one database!

charly78 · 08-04-2015, 12:50 PM

I am looking for examples on inserting data from different columns/Fields from tables into one database from 2 different databases

All the databases are in mysql. They are from a ecommerce shop and a point of sale system that happen to use mysql. I do not expect to use all the data but I want to move 800 + products over to this one database and some parts of the history and customer info. All of this data will end up into a ecommerce shop that has point of sale features.

I am slowly breaking down what fields match up.

I have experience with sql statements and have done some changes here and there even some inserts from one table to another but I am unable to find any examples of inserts from database1 tableA (field1,field2,field4,feild5,feild6,feild7,feild8...) to database2 tableb(simularfield1,simularfield2,simularfield4,simularfeild5,simularfeild6,simularfeild7,simularfe ild8...)

I would be essentially matching different fields in different tables to fields in tables in a different database.

If anyone could point me to or show me some examples of moving data maybe a way to avoid duplicates or if it finds some duplicates add the missing data.

Any good sh scripts I can edit or php whatever. I have been playing with workbench from mysql not finding a feature to do that or at least documentation. It crashes alot I figure just do it all in mysql cli is easiest.

That would be great.

Guttorm · 08-04-2015, 01:03 PM

https://dev.mysql.com/doc/refman/5.0/en/use.html

And you can change the select into something like this:

Code:

USE db1;
INSERT INTO db3.sometable (author,editor)
SELECT author_name,editor_name FROM author,db2.editor
  WHERE author.editor_id = db2.editor.editor_id;

MfromH · 08-06-2015, 03:29 AM

whenever you mention a tablename in the FROM clause you should prefix it by the schema name:
SELECT...FROM db1.tableA, db2.tableB...

if you select similar columns from two tables you can connect the two Selects by UNION :
CREATE TABLE db1.tableC AS
SELECT a, b, c, d FROM db1.tableA...
UNION
SELECT e, f, NULL, NULL FROM db2.tableB..

both Selects must have same number of columns, fill up with NULLs if columns are missing

NevemTeve · 08-06-2015, 04:49 AM

Are your 'databases' actually different databases (running on different computers) or just different schemas in the same database?

sundialsvcs · 08-06-2015, 08:45 AM

I'm thinking that you will probably wind up writing a small computer-program here . . .

MfromH · 08-07-2015, 06:27 AM

Quote:

Originally Posted by sundialsvcs

I'm thinking that you will probably wind up writing a small computer-program here . . .

I just want to be helpful about what to look for in the (excellent) mysql online manual.

charly78 · 08-08-2015, 06:45 PM

I will have to look into this two selects using UNION thats interesting. I am now wondering how to avoid duplicates and how I will work with primary and auto generating fields.

Than you Guttorm and MfromH !

They are 3 databases that I have put all on the same server all mysql.

One was from a windows Point Of Sale terminal that had my clients data locked into this pay $100+ each month and no freedom
Another from Zen cart

all to go into prestashop

Just wanted to keep the clients accounts and info and all of the data of products from the windows database. I mainly just grabbed the mysql data directory! and it seemed to allow me to the data when I copied it to a Linux mysql server.

lastly the entry of all the products with pictures and descriptions into the prestashop from zencart.

I am not intrested (but thankyou) in writing a small program. I might or would be intrested on how to make a shell script do this so if I had to do it again I could do it quicker. I might have to type 50 or so select or insert commands but will be in control of what goes where . I am going to try some attempts this weekend. (tonight) and then probaly if it goes well some smaller questions or if fails badly some bigger ones!

MfromH · 08-09-2015, 05:22 AM

to prevent duplicates from being listed then select DISTINCT... is your friend.

In a shell script you can use the mysql command with -e option to give a SQL statement to the server (in double quotes):
mysql -u user -ppasswd database -e "statement1;statement2;"

charly78 · 08-10-2015, 03:10 PM

I might not need to use 2 different databases. I am attempting to do 1 table from zendb to prestadb table that have similar but different fields .. I get this error I getting the feeling I have to outline the othere fields (is this necessary) so they have data? how do I do null on fields.

Code:

mysql> INSERT INTO prestadb.ps_product_lang (id_product,id_lang,description,name) 
    -> SELECT products_id,language_id,products_description,products_name FROM zencartdb.products_description
    -> WHERE products_description.language_id = prestadb.ps_product_lang.language_id;
ERROR 1054 (42S22): Unknown column 'prestadb.ps_product_lang.language_id' in 'where clause'

The Zen cart only has the main description and prestashop has short and long description in the similar table so will have to find a way to insert that into the proper spots

Code:

zencartdb
 TABLE `products_description` (
  `products_id` int(11) NOT NULL AUTO_INCREMENT,
  `language_id` int(11) NOT NULL DEFAULT '1',
  `products_name` varchar(64) NOT NULL DEFAULT '',
  `products_description` text,
  `products_url` varchar(255) DEFAULT NULL,
  `products_viewed` int(5) DEFAULT '0',
  PRIMARY KEY (`products_id`,`language_id`),
  KEY `idx_products_name_zen` (`products_name`)
) ENGINE=InnoDB AUTO_INCREMENT=808 DEFAULT CHARSET=utf8$$

to

prestadb
TABLE `ps_product_lang` (
  `id_product` int(10) unsigned NOT NULL,
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_lang` int(10) unsigned NOT NULL,
  `description` text,
  `description_short` text,
  `link_rewrite` varchar(128) NOT NULL,
  `meta_description` varchar(255) DEFAULT NULL,
  `meta_keywords` varchar(255) DEFAULT NULL,
  `meta_title` varchar(128) DEFAULT NULL,
  `name` varchar(128) NOT NULL,
  `available_now` varchar(255) DEFAULT NULL,
  `available_later` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_product`,`id_shop`,`id_lang`),
  KEY `id_lang` (`id_lang`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8$$

with just insert and no where clause

Code:

mysql> INSERT INTO prestadb.ps_product_lang (id_product,id_lang,description,name) 
    -> SELECT products_id,language_id,products_description,products_name FROM zencartdb.products_description;
ERROR 1062 (23000): Duplicate entry '1-1-1' for key 'PRIMARY'

I try now using the primary key as the where clause

Code:

mysql> INSERT INTO prestadb.ps_product_lang (id_lang,description,name) 
    -> SELECT language_id,products_description,products_name FROM zencartdb.products_description
    -> WHERE products_description.products_id = prestadb.ps_product_lang.id_product;
ERROR 1054 (42S22): Unknown column 'prestadb.ps_product_lang.id_product' in 'where clause'

Maybe it can not handle db.table.field ?

MfromH · 08-10-2015, 05:41 PM

Oh my goodness, this looks like a never ending story. Are you sure you don't want to involve professional help? Maybe you take some classes on SQL? There are so many beginners-mistakes in your code, I don't even know where to begin.

Anyway:

Quote:

Originally Posted by charly78

Code:

mysql> INSERT INTO prestadb.ps_product_lang (id_product,id_lang,description,name) 
    -> SELECT products_id,language_id,products_description,products_name FROM zencartdb.products_description
    -> WHERE products_description.language_id = prestadb.ps_product_lang.language_id;
ERROR 1054 (42S22): Unknown column 'prestadb.ps_product_lang.language_id' in 'where clause'

Well, as the ERROR says, there is no column language_id in prestadb.ps_product_lang. But there is a column called id_lang, (you already mentioned it in the INSERT line) maybe that is what you need to join the tables:

Code:

    -> WHERE products_description.language_id = prestadb.ps_product_lang.id_lang;

as I said.. maybe...

The result of this INSERT is a lot of new rows in your table, is that really what you want? If language_id=id_lang is indeed the correct join then its easy to fill up the rows in one table with parts of information of the other table, you should have a look at UPDATE in the manual.

Tomorrow we will have a look at the KEY columns of the tables and why its not wise to insert random values into them...

Greetings from H.

M.

astrogeek · 08-10-2015, 06:10 PM

Quote:

Originally Posted by charly78

I might not need to use 2 different databases. I am attempting to do 1 table from zendb to prestadb table that have similar but different fields .. I get this error I getting the feeling I have to outline the othere fields (is this necessary) so they have data? how do I do null on fields.

"Similar fields" is not sufficient, and inserting from one DB to another with similar tables may be little more than an exercise in generating tables full of garbage that looks like your data! A good outline would be the minimum that you need.

What you are actually trying to do is to migrate the data from one application to another while preserving the semantics (meaning) of that data to the target application. To do that you must work up a transform matrix that maps from one to the other preserving the meaning of the data, which will almost certainly include much more than a table to table and field to field copying of the data. It must also include the data key relations and constraints, domain info (types), etc. etc... all in a form meaningful to the target application.

Without the existience of such an explicit mapping as the starting point (even if it turns out to be nearly trivial) ...data in, garbage out will be the result.

You must either learn some basics of relational database modeling, or as MfromH says, engage with someone who has such knowledge. While many here will be glad to help with specifics, I fear that the scope of your project goes well beyond a question and answer session in a forum. All we can do is answer your questions, but if the questions are not well founded even correct answers will lead to trouble.