SQL Question

Status
Not open for further replies.
illusion

illusion

New member
Jun 24, 2006
3,475
35
0
Alright guys, quick question for my mysql friends. I have a database of jokes and that database already have roughly 600 entries in it. I recently came across another sql dump which I wanted to add to it. Problem is the sql dump and my database will clash because of the duplicate id entries.

My question to you is how does one add a sql dump to a pre-existing database without adding a certain column? Is there some way to stop a column from being added during a particular dump of a file?
 


I assume you have auto_increment on the ID column. Then just edit the dump of the new database in an text-editor and remove the parts where the ID are created.
Post a line that inputs a joke and I'll show you what to remove.
 
Yeah I know what to remove, I am just saying it is a 7k dump, so that is a fuckload of editing, I am all about keeping it quick, and editing 7,000 lines to get rid of the ID's just doesn't sound like my cup of tea
 
Just write a PHP script to do it for you. Won't take 5 minutes. Fetch the row from one, insert it into the other, the auto_increment will get it done for you.
 
something like...

$newJokes=mysql_query("SELECT * FROM newJokes");

while($joke=mysql_fetch_array($newJokes)){
mysql_query("INSERT INTO oldJokes VALUES(id, '{$joke['field']}', '{$joke['field']}', '{$joke['field']}')");
}
 
  • Like
Reactions: illusion
illusion said:
Yeah I know what to remove, I am just saying it is a 7k dump, so that is a fuckload of editing, I am all about keeping it quick, and editing 7,000 lines to get rid of the ID's just doesn't sound like my cup of tea
Click to expand...

Isn't that what search and replace is for. Sprinkle with a bit of regexp and you're golden.
 
Use the load database command to load the backup created with dump database . You can load the dump into a pre-existing database, or create a new database with the for load option. When you create a new database, allocate at least as much space as was allocated to the original database.
 
Hosting Comment said:
Use the load database command to load the backup created with dump database . You can load the dump into a pre-existing database, or create a new database with the for load option. When you create a new database, allocate at least as much space as was allocated to the original database.
Click to expand...
Dude that makes 0 sense.
 
Why don't you dump the new content into a separate table, then use
mySQL inserts to insert the data into the old table.

btw, when working with mySql db's I recommend using a desktop-based gui. It make's it much simpler to see the whole picture. You can try SQLyog. Thats what i'm using currently.

Cheers
 
illusion did you get it working? Since it's only a couple of fields what Stanley and I suggested will work wonders.
 
Just update your current IDs to be a different number, or change the dump to have a different number.

-update tblwhatever set id = id + 1000

-download TextPad - the text editor for Windows. Id is most likely the first character in the row, do search and replace regular expressions, replace \n with \n200 you just added 200 onto the fron of every id.
 
chatmasta said:
illusion did you get it working? Since it's only a couple of fields what Stanley and I suggested will work wonders.
Click to expand...
Yeah I got it working, Stanley's helped, just when that Hosting Comment guy posted I decided to leave this thread as it was going stale
 
the easiest thing for a non-coder to do would have been, import the new sql dump. This will create the table. Then go in and delete the ID column. Then dump this new "no-id" table to a sql file. Then upload this file into your current table. This would delete all the ids for you.
 
Status
Not open for further replies.
Top Bottom
Back