Please tell me how to merge two sql databases.
Do you know any tutorial about it?
What I want, is to insert data from sql I bought into sql which came with a
website's script. Sql I bought has only one table containing 4 fields: id,
name, ingredients, procedure.
Thank you in advance.
________________________________________________
http://www.photoclickspro.com/

Andy wrote:
> Please tell me how to merge two sql databases.

Huh? Sql databases? What kind?

> Do you know any tutorial about it?
> What I want, is to insert data from sql I bought into sql which came
> with a website's script. Sql I bought has only one table containing 4
> fields: id, name, ingredients, procedure.
> Thank you in advance.


Hoepfully you have a user with permission on both databases. If so, choose
to work on the 'extra' database and:

ALTER TABLE table_name RENAME normal_database_name.table_name
--
Rik Wasmus

"Rik" <luiheidsgoeroe [at] hotmail.com> wrote in message
news:581e0$45ad2fed$8259c69c$26539 [at] news1.tudelft.nl...
> Andy wrote:
>> Please tell me how to merge two sql databases.
>
> Huh? Sql databases? What kind?
>
>> Do you know any tutorial about it?
>> What I want, is to insert data from sql I bought into sql which came
>> with a website's script. Sql I bought has only one table containing 4
>> fields: id, name, ingredients, procedure.
>> Thank you in advance.
>
>
> Hoepfully you have a user with permission on both databases. If so, choose
> to work on the 'extra' database and:
>
> ALTER TABLE table_name RENAME normal_database_name.table_name
> --
> Rik Wasmus
>
>
>
It is MySql database.
Do you mean that I supposed to install a database I bought than work on
that? I thought that I can merge sqls in a special program.
If so please tell me where I supposed to write the code you posted? (ALTER
TABLE table_name RENAME normal_database_name.table_name)
I am new to sql database so I am not sure what to do?

Andy

Andy wrote:
> "Rik" <luiheidsgoeroe [at] hotmail.com> wrote in message
> news:581e0$45ad2fed$8259c69c$26539 [at] news1.tudelft.nl...
>> Andy wrote:
>>> Please tell me how to merge two sql databases.
>> Huh? Sql databases? What kind?
>>
>>> Do you know any tutorial about it?
>>> What I want, is to insert data from sql I bought into sql which came
>>> with a website's script. Sql I bought has only one table containing 4
>>> fields: id, name, ingredients, procedure.
>>> Thank you in advance.
>>
>> Hoepfully you have a user with permission on both databases. If so, choose
>> to work on the 'extra' database and:
>>
>> ALTER TABLE table_name RENAME normal_database_name.table_name

> It is MySql database.
> Do you mean that I supposed to install a database I bought than work on
> that? I thought that I can merge sqls in a special program.
> If so please tell me where I supposed to write the code you posted? (ALTER
> TABLE table_name RENAME normal_database_name.table_name)
> I am new to sql database so I am not sure what to do?

The two databases you have, the one you bought and the one you had, do they
have the same table names and column names?

--

//Aho

Andy wrote:
> "Rik" <luiheidsgoeroe [at] hotmail.com> wrote in message
> news:581e0$45ad2fed$8259c69c$26539 [at] news1.tudelft.nl...
>> Andy wrote:
>>> Please tell me how to merge two sql databases.
>>
>> Huh? Sql databases? What kind?
>>
>>> Do you know any tutorial about it?
>>> What I want, is to insert data from sql I bought into sql which came
>>> with a website's script. Sql I bought has only one table containing
>>> 4 fields: id, name, ingredients, procedure.
>>> Thank you in advance.
>>
>>
>> Hoepfully you have a user with permission on both databases. If so,
>> choose to work on the 'extra' database and:
>>
>> ALTER TABLE table_name RENAME normal_database_name.table_name
>>
>>
> It is MySql database.
> Do you mean that I supposed to install a database I bought than work
> on that?

Would seem the most logical thing to do. In what format did they deliver
this 'new' database to you? I assumed the database was already up and
running.

> I thought that I can merge sqls in a special program.

I have no doubt. I've never needed that though, so I'm not aware of them,
and moving just 1 table is so simple it wouldn't justify a download of any
program whatsoever.

> If so please tell me where I supposed to write the code you posted?
> (ALTER TABLE table_name RENAME normal_database_name.table_name)
> I am new to sql database so I am not sure what to do?


Well, you have 2 databases here, I assume they are on the same server? As
long as you've got a user for that database, that can connect to both
databases (i.e. select & alter etc.), you can use the rename syntax:
http://dev.mysql.com/doc/refman/5.0/en/rename-table.html

In php, that would be:

<?php
mysql_connect('server','user','password');
mysql_query('ALTER TABLE bought_database_name.table_name RENAME
normal_database_name.table_name');
?>

If they are not on the same server, it get's somewhat trickier. You could
use the infile / outfile possibilities:
http://dev.mysql.com/doc/refman/5.0/en/load-data.html

But let't not get into that just yet if you're just starting...
--
Rik Wasmus

"J.O. Aho" <user [at] example.net> wrote in message
news:514se1F1ia8h4U1 [at] mid.individual.net...
> Andy wrote:
>> "Rik" <luiheidsgoeroe [at] hotmail.com> wrote in message
>> news:581e0$45ad2fed$8259c69c$26539 [at] news1.tudelft.nl...
>>> Andy wrote:
>>>> Please tell me how to merge two sql databases.
>>> Huh? Sql databases? What kind?
>>>
>>>> Do you know any tutorial about it?
>>>> What I want, is to insert data from sql I bought into sql which came
>>>> with a website's script. Sql I bought has only one table containing 4
>>>> fields: id, name, ingredients, procedure.
>>>> Thank you in advance.
>>>
>>> Hoepfully you have a user with permission on both databases. If so,
>>> choose
>>> to work on the 'extra' database and:
>>>
>>> ALTER TABLE table_name RENAME normal_database_name.table_name
>
>> It is MySql database.
>> Do you mean that I supposed to install a database I bought than work on
>> that? I thought that I can merge sqls in a special program.
>> If so please tell me where I supposed to write the code you posted?
>> (ALTER TABLE table_name RENAME normal_database_name.table_name)
>> I am new to sql database so I am not sure what to do?
>
> The two databases you have, the one you bought and the one you had, do
> they have the same table names and column names?
>
> --
>
> //Aho
>

This is a print view of tables I have on my website's database and a print
view of a database I bought. (bottom one I just bought)
Sorry if it will not desplay correctly on newsgroup.

Show tables: `ad_groups`, `admin_control`, `admin_login`, `ads`,
`categories`, `emailed_recipes`, `ingredients`, `ratings`, `recipes`,
`search_terms`, `subscribers`, `users`
------------------------------------------------------------ --------------------

ad_groups
Field Type Null Default
groupid int(10) No 0
groupname varchar(255) No


Indexes: Keyname Type Cardinality Field
PRIMARY PRIMARY 3 groupid
groupid UNIQUE 3 groupid
groupid_2 INDEX None groupid


Space usage: Type Usage
Data 88 Bytes
Index 4,096 Bytes
Total 4,184 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 3
Row length ø 29
Row size ø 1,395 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM


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

admin_control
Field Type Null Default
approve_content char(1) Yes Y


Space usage: Type Usage
Data 5 Bytes
Index 1,024 Bytes
Total 1,029 Bytes
Row Statistic: Statements Value
Format fixed
Rows 1
Row length ø 5
Row size ø 1,029 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM


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

admin_login
Field Type Null Default
login varchar(20) Yes NULL
password varchar(20) Yes NULL
sessionkey int(10) Yes NULL


Space usage: Type Usage
Data 20 Bytes
Index 1,024 Bytes
Total 1,044 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 1
Row length ø 20
Row size ø 1,044 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM


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

ads
Table comments: Ads table

Field Type Null Default
adid int(10) No 0
image varchar(50) Yes NULL
url varchar(255) Yes NULL
code text Yes NULL
impressions int(10) Yes 0
clicks int(11) Yes 0
groupid int(10) Yes NULL
type char(1) Yes NULL
name varchar(255) Yes NULL


Indexes: Keyname Type Cardinality Field
PRIMARY PRIMARY 2 adid
adid UNIQUE 2 adid
adid_2 INDEX None adid


Space usage: Type Usage
Data 316 Bytes
Index 4,096 Bytes
Total 4,412 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 2
Row length ø 158
Row size ø 2,206 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 14, 2007 at 06:42 AM


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

categories
Field Type Null Default
categoryid int(10) No 0
category varchar(100) Yes NULL


Indexes: Keyname Type Cardinality Field
PRIMARY PRIMARY 11 categoryid
categoryid UNIQUE 11 categoryid
categoryid_2 INDEX None categoryid


Space usage: Type Usage
Data 224 Bytes
Index 4,096 Bytes
Total 4,320 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 11
Row length ø 20
Row size ø 393 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM


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

emailed_recipes
Field Type Null Default
sender_email varchar(255) Yes NULL
friend_email varchar(255) Yes NULL
recipeid int(10) Yes NULL


Space usage: Type Usage
Data 0 Bytes
Index 1,024 Bytes
Total 1,024 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 0
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM


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

ingredients
Field Type Null Default
recipeid int(10) No 0
ingredient varchar(255) No


Space usage: Type Usage
Data 30,656 Bytes
Index 1,024 Bytes
Total 31,680 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 841
Row length ø 36
Row size ø 38 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM


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

ratings
Field Type Null Default
recipeid int(11) No 0
rating float(10,2) No 0.00
ip varchar(50) Yes NULL


Indexes: Keyname Type Cardinality Field
recipieid INDEX None recipeid


Space usage: Type Usage
Data 48 Bytes
Index 2,048 Bytes
Total 2,096 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 2
Row length ø 24
Row size ø 1,048 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 11:46 PM


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

recipes
Field Type Null Default
recipeid int(10) No 0
title varchar(255) Yes NULL
recipe text Yes NULL
userid int(10) Yes NULL
rating int(10) Yes 0
categoryid int(10) Yes NULL
date varchar(20) No
story text Yes NULL
hits int(10) Yes 0
status char(1) Yes W


Indexes: Keyname Type Cardinality Field
PRIMARY PRIMARY 105 recipeid
recipieid UNIQUE 105 recipeid
recipieid_2 INDEX None recipeid


Space usage: Type Usage
Data 58,668 Bytes
Index 4,096 Bytes
Total 62,764 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 105
Row length ø 558
Row size ø 598 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 14, 2007 at 06:31 AM


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

search_terms
Field Type Null Default
term varchar(255) Yes NULL
count int(10) Yes 0


Space usage: Type Usage
Data 20 Bytes
Index 1,024 Bytes
Total 1,044 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 1
Row length ø 20
Row size ø 1,044 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:18 AM


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

subscribers
Field Type Null Default
emailaddress varchar(255) Yes NULL


Space usage: Type Usage
Data 24 Bytes
Index 1,024 Bytes
Total 1,048 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 1
Row length ø 24
Row size ø 1,048 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM


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

users
Field Type Null Default
userid int(10) No 0
login varchar(50) No
password varchar(50) No
email varchar(255) Yes NULL
newsletter int(1) Yes NULL


Indexes: Keyname Type Cardinality Field
PRIMARY PRIMARY 1 userid
userid UNIQUE 1 userid
login
userid_2 INDEX None userid
login


Space usage: Type Usage
Data 40 Bytes
Index 4,096 Bytes
Total 4,136 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 1
Row length ø 40
Row size ø 4,136 Bytes
Creation Jan 05, 2007 at 09:11 AM
Last update Jan 05, 2007 at 09:11 AM



Tables I bought:

recipes
Field Type Null Default
id bigint(20) No
title varchar(255) No
category varchar(100) No
incredients text No
procedures text No
imagepath varchar(255) No
notes varchar(255) No
status varchar(15) No Active


Indexes: Keyname Type Cardinality Field
PRIMARY PRIMARY 571 id


Space usage: Type Usage
Data 506,804 Bytes
Index 9,216 Bytes
Total 516,020 Bytes
Row Statistic: Statements Value
Format dynamic
Rows 571
Row length ø 887
Row size ø 904 Bytes
Next Autoindex 621
Creation Jan 12, 2007 at 03:50 PM
Last update Jan 12, 2007 at 03:59 PM

This is a table of database I bought:

recipes
Field Type Null Default
id bigint(20) No
title varchar(255) No
category varchar(100) No
incredients text No
procedures text No
imagepath varchar(255) No
notes varchar(255) No
status varchar(15) No Active

And this are fields I want to transfer to my recepies database I had:

Tables I bought:

recipes
Field Type Null Default
id bigint(20) No
title varchar(255) No
category varchar(100) No
incredients text No
procedures text No

Andy wrote:
> This is a table of database I bought:
>
> recipes
> Field Type Null Default
> id bigint(20) No
> title varchar(255) No
> category varchar(100) No
> incredients text No
> procedures text No
> imagepath varchar(255) No
> notes varchar(255) No
> status varchar(15) No Active
>
> And this are fields I want to transfer to my recepies database I had:
>
> Tables I bought:
>
> recipes
> Field Type Null Default
> id bigint(20) No
> title varchar(255) No
> category varchar(100) No
> incredients text No
> procedures text No

Ah, appending to an already existing table, with a different format, that's
another beast, forget all earlier posts.

It also has nothing to do with php, so in future you might want to ask in
comp.databases.mysql
(On a side note, you paid for a database that has a field named
'incredients'? If they're that careless in naming the fields, I wonder
wether the data is even worth it...)

But to answer your problem: you'll need several queries, some of which can
be tricky.

You do not want to transfer the id, because your table already has an id,
and it will overwrite earlier ones as it is a primary key. We will not use
the id from the new table.

Also, a category here is a description, while in your normal database it's
a key, pointing to a description, so you'll have to insert all category
fields that aren't yet present in the original database. On inserting the
recipe, you'll have to check which category id they will get.

Also, 'incredients' is text here, while in your database 'ingredients' is a
seperate table. Without any knowledge what those 'incredients' actually
hold I cannot tell you how to do this.

I guess 'procedures' from the new table should go in 'story' in the old
table? Hard to say without their content.

In short: without access to the database/knowledge about the actual
contents, it's not doable for anyone here to give you the queries to do it
(mainly because of the ingredients part). There's no such thing as a
program which will automate this for you, as it would have to have magical
knowledge about the relevance of certain fields in different tables.
There's also no short tutorial which will explain it to you, just learn
some SQL, try with trail and error, and enjoy the learning experience. It
will be a great benefit in the future. If your hesitant to play with your
'live' database, just copy some tables under a different name, and play
with those untill your comfortable you can swap them with the live ones.

If you feel you cannot succeed, or you want this online asap, either give
someone you know with knowledge of SQL access to the database, or pay
someone to do this. I'd think anyone with direct access to the database
will be able to do this within half an hour or so. Unfortunatly, this is
not something I can do for free here on the newsgroups.
--
Rik Wasmus

Andy wrote:
> This is a table of database I bought:
>
> recipes
> Field Type Null Default
> id bigint(20) No
> title varchar(255) No
> category varchar(100) No
> incredients text No
> procedures text No
> imagepath varchar(255) No
> notes varchar(255) No
> status varchar(15) No Active
>
> And this are fields I want to transfer to my recepies database I had:
>
> Tables I bought:
>
> recipes
> Field Type Null Default
> id bigint(20) No
> title varchar(255) No
> category varchar(100) No
> incredients text No
> procedures text No

--- this is your own recipes table ---
recipes
Field Type Null Default
recipeid int(10) No 0
title varchar(255) Yes NULL
recipe text Yes NULL
userid int(10) Yes NULL
rating int(10) Yes 0
categoryid int(10) Yes NULL
date varchar(20) No
story text Yes NULL
hits int(10) Yes 0
status char(1) Yes W

--- eof ---

Okey, I assume you already have data in the, which makes you already will have
items with a 'recipeid', this makes trouble as the bought ones will also have
'id', then you have the trouble and do you have auto_increment for your
'recipeid', are your current 'categoryid' the same values as the bought
'category'.
Is 'recipe'='incredients'?
Is 'story'='procedures'?

I assume the bought database came in a sql file, then the easist would really
you used the find&replace function in a text editor (it has to manage to save
pure text files), replace the 'recipes' to say 'bought_recipes', as this will
make things a lot easier. Remove text that drops and creates database, should
be at the top of the file. ALWAYS MAKE A COPY OF THE ORIGINAL FILE BEFORE YOU
START EDITING.

You will need to inject that file to the mysql server, write this on one line,
and change the "name_of_your_database" to the name of the database where you
have your site data. change "mysql_user" to the username you have to login to
the database (see there is no space between -u and the name) and in the same
manner change the "password" to the real password (notice there is no space
between -p and the password) and last change the "modified_bought.sql" to the
name of the file you save the modifications I wrote before.

mysql name_of_your_database -umysql_user -ppassword < modified_bought.sql


When you have done that, you need to login into the mysql server (you can use
myphpadmin if you have that or the mysql command), select the database where
the two tables are located and do the following:

INSERT INTO recipes(`title`, `categoryid`, `recipe`, `story`,`date`) SELECT
`title`, `category`, `incredients`, `procedures`, CURDATE() as `date` FROM
bought_recipes;

We have assumed that recipeid used auto_increment, which is the smart thing to
do, this will lead to new id numbers for the bought recipes, if your database
don't use auto_increment, then you need to use:

INSERT INTO recipes(`recipeid`, `title`, `categoryid`, `recipe`,
`story`,`date`) SELECT `id`, `title`, `category`, `incredients`, `procedures`,
CURDATE() as `date` FROM bought_recipes;

Now you will most likely get the trouble that you can have two or more recipes
with the same id number, which is really bad.

--

//Aho

Thank you Rik and Aho for your help. I will follow your advice and try to
solve my problem.
I hope I'll be lucky, if not, company I boght a database will merge my
database for about $50. But I want to do it myself so I will learn some new
things.

Sincerely.
Andy