Any SQL gurus in the house? Can you help?
 

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.

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?

select * into db2 from db1 where (id <= 201 and id >= 1000)?

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)"

Won't work if the IDs that are in one but not the other aren't sequential :upsidedow

Have to use a join (right, left, outer, whatever way you prefer hehe) in that case.

EDIT: My bad, he did say they are starting with 201 heh. So nevermind.

are you using INNODB? myisam does not support transactions

INSERT INTO db2 SELECT * FROM db1 WHERE id NOT IN (SELECT id FROM db2)

why didn't "insert ignore" work?

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 database

Well, that's one way to entirely avoid the hassle of, uh, the database part. :thumbsup

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 NULL

sub-selects? YIKES!

that doesn't work if he's in INNODB...
and uhm... doing some file based process to fix database issues is just asking for problems.

not to mention, db files are binary... he's not doing a local RSYNC or something...

:)

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.

You all make shit way to complex.

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.

You smell like Grandma.

I had 4 beers tonight, so Im not touching it until tomorrow, but please tell me why "rollback" isn't working for MySql. Is it oracle only feature? does it do commit on it own???

databases.. each has identical structure.. I want to combine entries...

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)

:1orglaugh:1orglaugh

thanks man...

http://www.tonyspencer.com/mt/archives/borat.jpg

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.

A CSV file still has "data" in it! :D

funny.. I was thinking something like that about excel

http://spreadsheets.about.com/od/dat...l_database.htm

Or just select all from the 1000 and select all from 200 where id = id and if it don't match, insert

Yep. Pretty quick, simple and straight forward.

And your issue with sub-selects is ??

some real geniuses here :(

well if the tables in each DB are the same.. then..

INSERT INTO
db1.table
(*)
SELECT
t2.*
FROM
db1.table t2
LEFT OUTER JOIN db2.table t1 ON (t1.id = t2.id)
WHERE
t1.id IS NULL


just make sure the permissions of the user you are runnign this as has access to both db's.

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-selects

quicker?
hehe, obviously you're a *nix guy and not a DBA...

Yeah, but this sounds like he wanted something for one time use not something that was going to be running in an ongoing process.

When it is one time use take the easiest route.

actually I was a sr consultant for Informix for yrs but ya I got lots of UNIX too thing is my informix days were back in 1993 and I havent really kept up with RDBMS since...

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 :)

Yes, stop fucking stringing us along and tell us why INSERT IGNORE is not an option.

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.

It's still poison. ;)

Shouldn't this whole thing been in the webmaster forum anyways? heheh

Anyways.. COMKAT, there are like 3 solutions in here...

pick one of them.
say thanks.
close the thread.
:)

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.

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.