...

View Full Version : Merge 2 Tables from different databases under the same user



lorenzofln
03-19-2013, 01:01 AM
Hi guys, I am stucked here... I need help please..

I dont know why I am getting this error, I think this morning i ran this query without errors and it worked.. I am trying to merge two different tables from two different databases under the same user into one table, the tables have the same fields, but different values, and I want a big table with the fields from the 2 merged tables.
I am using:

CREATE TABLE students_merged engine = MERGE UNION = (db1.students_contacts,db2.students_contacts);

And i get

"#1113 - A table must have at least 1 column " :mad:

Do you know what I am doing wrong, please?

BubikolRamios
03-19-2013, 01:15 AM
http://dev.mysql.com/doc/refman/5.1/en/merge-storage-engine.html:


CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;


Newer used that, but looks like you have to state columns of that new table.
And you did not specify any.

anyway I would do something like this:



create table students_merged as select * from db1.students_contacts;
insert ignore into students_merged select * from db2.students_contacts;

lorenzofln
03-19-2013, 02:51 PM
hello again, thanks for your help, please is there any way to include a order by field (date) when merging the tables and also a way to run this query only to update the merged tables with new records from the other two tables? please help me, thank you so much.

BubikolRamios
03-19-2013, 03:30 PM
order by when merging is pointles, you can always do order by when geting data from merged table



only to update the merged tables




insert ignore into


The key here is ignore keyword.

As per key in your merged table, which should be the same as in source tables, by my recipie, duplicates(that is, records that you already imported)
will be ignored.

so you would do, to do update only



insert ignore into students_merged select * from db1.students_contacts;
insert ignore into students_merged select * from db2.students_contacts;


and run that any number of times and it will transfer only new records, that is, if you set key(index) right & up to your desires.

If you don't understand (which I think you dont) what i'm telling you, paste here create code of students_merged table.

lorenzofln
03-19-2013, 04:00 PM
create table students_merged as insert ignore into students_merged select * from db1.students_contacts;
insert ignore into students_merged select * from db2.students_contacts;

is this right??? lol

BubikolRamios
03-19-2013, 05:45 PM
No . As I said, give as your students_merged table strcture.

lorenzofln
03-19-2013, 06:32 PM
I am sorry, I dont understand :(, can you give me just the code? Please..

BubikolRamios
03-19-2013, 06:40 PM
Well it is like this, in order to ask me a question about potatoes you must know ,approximately at least,what potatoes are, right ?

So get forinstance this: http://www.heidisql.com/download.php

with that you will see your db & tables & specific table create code.

Without that, I can write a novel about your problem with no usable result whatsoever .

lorenzofln
03-19-2013, 08:43 PM
hello my friend, yes you are right.. I saw now..

well, i understood that the main problem is that both databases has a column named "id" that is incremental, and when i merge them, the ID column has duplicates, and when i try to see them on our software, duplicates are ignored, so basically i see only the main table, like I didnt merged the second one..

is there any way to merge those tables and change the incremental "id" field for the new records? i mean, if table 1 has 1000 entries, and table 2 has 200 entries, i would like table 3 to have 1200 entries each one with a unique valeu in the id field, did you understand me???? thanks in advance as ALWAYS...

BubikolRamios
03-19-2013, 09:08 PM
Ok you figured you have auto inc field which is a key. I'm not a spoon bender to
figure out what other key fields you will pull out in next zilion posts.

You should understand one thing about this forum. It is help me understand and not do it for me.

Anyway, I'm willing to do it for you if you provide table structure. <-- THIS IS RED FOR A REASON !
As I told you.

Otherwise you are wasting my(and anyone else looking at this thread) time.

There is a hint: look at second post in this thread, there is sample create table code (in first code box)

lorenzofln
03-19-2013, 09:37 PM
Thank you my friend, I swear I am studying mysql and php... I WANT to understand.. but sometimes time is soooo short...

let me see if this can help you.. Key and Extra columns are empty in all fields..

Field Type Null Default
id int(11) NO 0
product_id int(11) NO NULL
user_id int(5) NO NULL
date datetime NO NULL
name varchar(255) NO NULL
email varchar(255) NO NULL
phone varchar(100) NO NULL
address varchar(255) NO NULL
city varchar(255) NO NULL
state varchar(255) NO NULL
cp varchar(100) NO NULL
text text NO NULL
userfile varchar(255) NO NULL
layout varchar(255) NO NULL

BubikolRamios
03-19-2013, 09:44 PM
where are indexes, keys ?
To quote again, how should create table code look like.


CREATE TABLE total (
-> a INT NOT NULL AUTO_INCREMENT,
-> message CHAR(20), INDEX(a))
-> ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;

Old Pedant
03-19-2013, 09:58 PM
So the "secret" here is to first create a table of that same structure.

Fortunately, there is a *TRIVIAL* way to do that with MySQL:


CREATE TABLE students_merged LIKE db1.students_contacts;

And then it's easy to put the first table into that:


INSERT INTO students_merged
SELECT * FROM db1.students

But then to put in the *second* table, you must specify *ALL* the fields *EXCEPT* the auto_increment field.

Like this:


INSERT INTO students_merged ( product_id, user_id, ...., layout )
SELECT product_id, user_id, ..., layout FROM db2.students

NOW...

(1) That will give *NEW* id values to all the students from db2.students. When you don't specify the id, and that field is auto_increment, it will just assign the next number. If that's not going to work for you--if you need to retain the old id numbers for some reason--then we have to do something different.

(2) That all assumes that you do not need to worry about duplicates. If you do, we can resolve that, too. But what *constitutes* a duplicate, in your world? Can't help until you call that out in clear language.



You would need to tell us in very explicit terms.

lorenzofln
03-19-2013, 10:39 PM
Sorry my friend, I really want to understand, but I do not understand what you are asking me.

Indexes, I dont know where to find indexes.

Keys, the Keys column in mynable structure is empty, I wrote this before, all fields of the Keys column are empty in the table view.

You asked me the table structure, right? I thought I had given you the table structure in my last post, there are no keys because they are empty and about indexes, well, I dont know where to find them.

Please tell me exactely what you need and i will do My best..... sorry for being so hard for me to understand :(

Old Pedant
03-19-2013, 11:03 PM
I don't think you need to worry about keys and indexes is you use my suggestion: Creating one table LIKE another clones those, as well.

But for reference: The best way to get a *FULL* description of a table in MySQL is to use the SHOW CREATE TABLE command.

So if you used SHOW CREATE TABLE db1.students; that would give us all available information about the table.

lorenzofln
03-20-2013, 05:27 PM
Thank you my friend, I ran the command, here is the text:

CREATE TABLE `jos_students_contacts` (
`id` int(11) NOT NULL DEFAULT '0',
`product_id` int(11) NOT NULL,
`user_id` int(5) NOT NULL,
`date` datetime NOT NULL,
`name` varchar(255) NOT NULL,
`email` varchar(255) NOT NULL,
`phone` varchar(100) NOT NULL,
`address` varchar(255) NOT NULL,
`city` varchar(255) NOT NULL,
`state` varchar(255) NOT NULL,
`cp` varchar(100) NOT NULL,
`text` text NOT NULL,
`userfile` varchar(255) NOT NULL,
`layout` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Old Pedant
03-21-2013, 12:56 AM
WOW! You are right! You have *NO* INDEXES!

How do you manage to prevent duplicate INSERTs even into that one table???

Well, my remarks still stand.

Is your other table identical? That is, does it have the same field names and types? If not, maybe you need to use SHOW CREATE TABLE on it as well?

BubikolRamios
03-22-2013, 04:22 AM
To hopefuly clarify to you some things.

to merge two tables with any chance of good result:

1. they have to have identical structure
2. merged table must have the same structure as the source ones
3.Imagine you have only one field in both source tables, and only one record and contence of it is in both 'john doe'

4. what do you expect in merged table ?

'john doe'
'john doe'

or just

'john doe'

?

The second option would be choice for most people on this forum.
Hence the merged table has to have index !
In order that insert ignore command would work --> prevent duplictaes of 'john doe' in merged table.

And as you showed, there is no key/indexes in your source table. If you have a table with 1000 and one with 200, they should merge with no prob into 1200 records table.
Of course there will be a mess with id-s, ....

There, I told you same as old pedant, from another angle, hope it helps.

lorenzofln
03-22-2013, 03:01 PM
To hopefuly clarify to you some things.

to merge two tables with any chance of good result:

1. they have to have identical structure
2. merged table must have the same structure as the source ones
3.Imagine you have only one field in both source tables, and only one record and contence of it is in both 'john doe'

4. what do you expect in merged table ?

'john doe'
'john doe'

or just

'john doe'

?

The second option would be choice for most people on this forum.
Hence the merged table has to have index !
In order that insert ignore command would work --> prevent duplictaes of 'john doe' in merged table.

And as you showed, there is no key/indexes in your source table. If you have a table with 1000 and one with 200, they should merge with no prob into 1200 records table.
Of course there will be a mess with id-s, ....

There, I told you same as old pedant, from another angle, hope it helps.

Hello my friend. Sorry for the delay, my daughter went to hospital, but everything is good now..

Look, the tables have identical structure, and in merged table i just want the id to be changed to a unique id, because it doesnt matter for me if the field name has 2 identical value, because it could be related to 2 different leads, i mean that i would like to have the row removed only if ALL FIELDS are identical, it does not matter if only one of them is identical, i want the duplicate to be remove only if all fields are identical, and i need a unique id per row.

when i am talking about removing duplicates, the issue is that i will have to run this merging query everyday and i will need to process only new items, new leads only, to have only 1 table with all leads.. so i expect it to be

john doe lead 1
john doe lead 2

but if ALL THE FIELDS are identical, then i expect to have only

john doe

did you understand my problem now? many thanks, really.

BubikolRamios
03-22-2013, 03:37 PM
well, i do know what your problem is exactly, all the time, as old pedant do, do nnt doubt of that at all (-: But you are forcing it all the time in the wrong way. Wrong way is not having any index on source tables, and you don't want to hear that or you don't understand what index is all about.

This is how your merged table should be:



CREATE TABLE `merged` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`product_id` INT(11) NOT NULL,
`user_id` INT(5) NOT NULL,
`date` DATETIME NOT NULL,
`name` VARCHAR(255) NOT NULL,
`email` VARCHAR(255) NOT NULL,
`phone` VARCHAR(100) NOT NULL,
`address` VARCHAR(255) NOT NULL,
`city` VARCHAR(255) NOT NULL,
`state` VARCHAR(255) NOT NULL,
`cp` VARCHAR(100) NOT NULL,
`text` TEXT NOT NULL,
`userfile` VARCHAR(255) NOT NULL,
`layout` VARCHAR(255) NOT NULL,
UNIQUE INDEX `Index 1` (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM;




As you described it in last post , merged table shoud have key on all fields, and since tere are so many fields that is bad all together, furthermore mysql will not let you do that:

forinstance, this:


ALTER TABLE `merged`
ADD INDEX `Index 2` (`address`, `city`, `state`, `phone`, `product_id`, `date`, `cp`, `text`(100), `userfile`, `name`, `layout`, `user_id`, `email`);


will pop up:


SQL Error (1071): Specified key was too long; max key length is 1000 bytes


So to be able to make key ona all fields you should try to downsize them, but ,for sure ,that will lead to nowhere.

Without key,you can do it programticaly with application, but that would be 'super dumb'.

bottom line, decide which fields are necessary for key, if I ignore all the rest.

Not to mention, the table of yours should be splitted into two tables at least,
I mean what is product_id doing in students table at all .



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum