I have two identical structure DBs. One has 1000+ records (DB1) the other one that has 200 (DB2). I would like to combine those two by adding 1000 records to smaller DB skipping duplicates (based on primary key - ID).. so basically moving 800 records starting with ID=201 from DB1 to DB2
-
If the primary key is unique you could do a regular insert and mysql will just throw an error and continue when it sees a duplicate id.I like pie. -
mysql will error out n that. Im using insert ingnore should work but I'm missing something there. screw it, will do it tomorrow.
BTW, why isn't "rollback" command working in MySql?Comment
-
-
1. Get the list of ids on server #1
2. Combine them into a comma separated list: join(', ', $theList);
3. "SELECT * FROM table WHERE id NOT IN ($theList)"Comment
-
-
-
INSERT INTO db2 SELECT * FROM db1 WHERE id NOT IN (SELECT id FROM db2)All cookies cleared!Comment
-
why didn't "insert ignore" work?
Comment
-
im not sure I see the problem a simple unload of one database to a file then insert it into the other. If you want more control unload both to flat files, merge and sort (UNIX) then and use the uniq command(UNIX) to dedupe the merged file then load it into a databaseMike South
It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.Comment
-
uhm... you said "DB1" and "DB2".. so.. I'm gonna assume you actually mean databases, although it seems more likely you mean tables... anyways.
INSERT INTO
db1.table
(field1,field2,field3)
SELECT
t2.field1,
t2.field2,
t2.field3
FROM
db1.table t2
LEFT OUTER JOIN db2.table t1 ON (t1.id = t2.id)
WHERE
t1.id IS NULLModels/Agency search: http://strongthread.comComment
-
-
-
I pray to god he was at least assuming doing two dumps.. but the whole idea of doing that just has to be tongue in cheek.Comment
-
You all make shit way to complex.
PHP-MySQL-Rails | ICQ: 342500546Comment
-
nah its a cakewalk and way faster than cross database transfers assuming his dbms even allows such things
I also assumed he really meant databases and not relations (tables)
It mostly just a matter of understanding your data dbload and dbunload seem simplest.Mike South
It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.Comment
-
-
Comment
-
export both into excel
and select all data then goto toolbar Data > Filter > Advanced Filter
click copy to another location change the copy to range ... click unique records only then hit ok
but hey, I'm no SQL guru :p
(btw this will only work for up to like 65k, excels row limit)Last edited by PPC; 09-24-2008, 11:10 PM.
Pay Per Click Comment
-
-
Or, ya know, you could just take my query and insert the field/values but NOT the auto_increment key field for X records.
But, hey, I haven't seen so many convoluted ways to do database transactions OUTSIDE of a database in.. forever.Comment
-
funny.. I was thinking something like that about excel
http://spreadsheets.about.com/od/dat...l_database.htmC:\Code\
C:\Code\Run\Comment
-
Or just select all from the 1000 and select all from 200 where id = id and if it don't match, insertComment
-
some real geniuses here
Don't let greediness blur your vision | You gotta let some shit slide
icq - 441-456-888Comment
-
-
they are super slow, bad practice. I wish MySQL never put that damn functionality into itself.
when I have to fix other peoples legacy code.. finding queries that take 14 seconds for no reason at all.. it's usually due to sub-selects.
OR bad index's...
but most sub-selectsModels/Agency search: http://strongthread.comComment
-
-
Mike South
It's No wonder I took up drugs and alcohol, it's the only way I could dumb myself down enough to cope with the morons in this biz.Comment
-
You could always use INSERT IGNORE also, to just try and insert them all from db1 and it will error on any duplicates (primary key), BUT keep going and not stop if you are using INSERT IGNORE
Skype variuscr - Email varius AT gmailComment
-
Yes, stop fucking stringing us along and tell us why INSERT IGNORE is not an option.--
reactComment
-
either way, you're way doesn't work...
cuz if he's using INNODB, it can't be done outside of SQL.
if he's using INNODB then he wouldn't need any sort of comparison, he could just flat out copy the file from one DB to the other.
AND that's all assuming he has access to the server, not just SQL access. AND that's assuming he knows how to navigate around *nix at all.Models/Agency search: http://strongthread.comComment
-
-
Shouldn't this whole thing been in the webmaster forum anyways? hehehModels/Agency search: http://strongthread.comComment
-
Anyways.. COMKAT, there are like 3 solutions in here...
pick one of them.
say thanks.
close the thread.
Models/Agency search: http://strongthread.comComment
-
Ok, figured it out. The reason insert ignore didn't work is because when I did export into sql file through phpMyAdmin, it added extra line with ; in it ... don't know why.
so thank you, and its closed.Comment
-
Forget the export..
insert ignore into table2 select * from table1;
or if you have a disparate structure:
insert ignore into table2 (some, fields, here) select some, fields, here from table1;
Glad it worked out.--
reactComment
AIM: GrouchyGfy
Comment