[Guide] How to easy fix old sql and/or copy old content from old db,s

[knaur]

Presents My Guide To Convert Old SQL Files and/or copy content from old db,s to new.

By Knaur & Norwegian Elite Team ©2008

Ok, I was helping Chrispee out with an old creature spawn table from 2.4.x to 3.0.3
and he asked me if i could make an Guide for those that has problems with these parts.

So ill make these small easy Guides for those that needs them.

Guide 1: Repairing old big sql,s to work on new structures:

Ok, ill make an easy samle of what i do.
Chrispee sent me an creature spawn sql that had many spawns:
(im showing the 7 first)

INSERT INTO `creature_spawns` VALUES ('4660455', '900031', '1', '5229.06', '-1239.48', '1374.85', '3.69046', '0', '3007', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
INSERT INTO `creature_spawns` VALUES ('4660456', '900031', '1', '5219', '-1234.49', '1374.85', '5.89742', '0', '3007', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
INSERT INTO `creature_spawns` VALUES ('4660457', '900031', '1', '5221.93', '-1228.19', '1374.85', '5.42226', '0', '3007', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
INSERT INTO `creature_spawns` VALUES ('4660458', '900031', '1', '5233.49', '-1233.77', '1374.85', '3.03072', '0', '3007', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
INSERT INTO `creature_spawns` VALUES ('4660459', '900031', '1', '5229.87', '-1228.2', '1374.85', '4.24809', '0', '3007', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
INSERT INTO `creature_spawns` VALUES ('4660460', '900031', '1', '5221.39', '-1242.52', '1374.85', '4.15777', '0', '3007', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');
INSERT INTO `creature_spawns` VALUES ('4660461', '900031', '1', '5219.14', '-1248', '1378.6', '3.96928', '0', '3007', '0', '0', '0', '0', '0', '0', '0', '0', '0', '0');

Ok, first we need to find out how many new fields that has been added or removed.
2 ways to do it:

1: count fields in table, then in one of the lines here.
or
2: copy one line into your sql editor and add one and one , '0' at end of sql until it executes without error.

(Works in 95% of cases, cause new fields mostly gets added at end.)

Ok, so in this case, they have increased creature_spawn with 6 fields.
so, how fix all queries fast? its a large file, and manually would take way of time.
Actually its so easy that most can do it without problem

*Open sql in Notepad
* choose Edit and Replace


* Ok, copy , '0'); from one of the queries and paste it into "find what" field
* paste same , '0'); into "replace with" field
* Now we gonna add 6 , '0' more to the "replace with" field, the result shall look like this: , '0', '0', '0', '0', '0', '0', '0'); 6 + the old)


Then just press "Replace All" button and whole query sql will have 6 new fields added in every query
Work Done.

Its no guarantee that it will work correct on all kind of tables,
if new data is added other places, but mostly this works as charm.

------------------------------------------------------------------------------------------------------------------------



Guide 2: Copy old content from older db,s to newer db,s:

Ok, this is a bit more advanced, and need more work
but its not so hard as many thinks.
I have even converted items and mobs from old pre-tbc into new db,s this way.
Thats the way i still have all my old items from pre-tbc databases in my releases.
This is a ok way if you dont have update sql,s for tables.
and they are hard to find if you have a real old db you wanna use.

*Install the old database into mysql
*Install the new database into mysql

*Use SQLYog to this, and open 2 of them. 1st at left of screen and 2nd at right.
*Open old db in left program and new db in right side.
* Choose the table you want to convert and move
*In this case we will use Items table.


*Rightclick items table on old table an choose "Alter Table". move the new window at top of old db.
*Do the same on new db and move window over new db.


* Now, you can easy add the new missing fields in the old, simply by look at right side, and type it in at left side.
*When this is done, the old table is ready for move.
*Rightclick again on items in old db. Choose backup/export, and then Choose Export table data to CSV, SQL, Excel, etc...


*Choose CSV field and go at bottom and choose save as field, and type in the name you want for CSV file like items.csv.. and press Export button


*Now go to new db, and choose item table and rightclick it. choose Import data from CSV. Choose the items.csv and press Import.


*It will now copy all items/id,s you dont have from before, and skip the items/id,s you have from before into the new db.

And Voila, you have now imported items.

The CSV Method can also be used to easy copy stuff from other new repacks and so on to get npc,s items, vendors, quests+++ that other has made.
It will automatic skip all you have from before.

I know most of you repackers knows this already,
an even better ways, but well, this is the way i do it hehe,
hope some find it useful anyway, i know Chrispee will hehe

[pryd]

Just wanted to say thanks for the inspiration. After examining your guide I took a few hours to test this and it works like a charm.

I was able to import all the great new items from ArcEmu repacks into my Mangos!
JOY! It almost fixed my enchanting .. sigh. Still looking into that.

+Rep

[Chrispee]

As always mate u impress me nice guide this is way more easy than i used to do

[lowgrant]

good guide!

[knaur]

Just wanted to say thanks for the inspiration. After examining your guide I took a few hours to test this and it works like a charm.

I was able to import all the great new items from ArcEmu repacks into my Mangos!
JOY! It almost fixed my enchanting .. sigh. Still looking into that.

+Rep

hehe tnx, yeah its a good way to convert to mangos too

chrispee and lowgrants: tnx