469,890 Members | 2,164 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

Merging databases

Ike
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA, but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?

Thanks, Ike
Jul 20 '05 #1
15 2791
Ike spilled the following:
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,
but with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


This kinda suggests that you didn't normalize your database.

To answer your question....

1) do you need to ....
(
SELECT 'A' AS Branch, BranchA.* FROM BranchA WHERE $criteria
) UNION (
SELECT 'B' AS Branch, BranchB.* FROM BranchB WHERE $creiteria
)
2) if you really have to then...
DELETE FROM BranchToT;
INSERT INTO BranchToT SELECT * FROM BranchA WHERE $criteria
REPLACE INTO BranchToT SELECT * FROM BranchB WHERE $criteria
I'll leave you to work out why the third statement in the above might work
better with a REPLACE instead of an INSERT.

C.

Jul 20 '05 #2
Ike spilled the following:
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,
but with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


This kinda suggests that you didn't normalize your database.

To answer your question....

1) do you need to ....
(
SELECT 'A' AS Branch, BranchA.* FROM BranchA WHERE $criteria
) UNION (
SELECT 'B' AS Branch, BranchB.* FROM BranchB WHERE $creiteria
)
2) if you really have to then...
DELETE FROM BranchToT;
INSERT INTO BranchToT SELECT * FROM BranchA WHERE $criteria
REPLACE INTO BranchToT SELECT * FROM BranchB WHERE $criteria
I'll leave you to work out why the third statement in the above might work
better with a REPLACE instead of an INSERT.

C.

Jul 20 '05 #3
Ike spilled the following:
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,
but with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


This kinda suggests that you didn't normalize your database.

To answer your question....

1) do you need to ....
(
SELECT 'A' AS Branch, BranchA.* FROM BranchA WHERE $criteria
) UNION (
SELECT 'B' AS Branch, BranchB.* FROM BranchB WHERE $creiteria
)
2) if you really have to then...
DELETE FROM BranchToT;
INSERT INTO BranchToT SELECT * FROM BranchA WHERE $criteria
REPLACE INTO BranchToT SELECT * FROM BranchB WHERE $criteria
I'll leave you to work out why the third statement in the above might work
better with a REPLACE instead of an INSERT.

C.

Jul 20 '05 #4

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA, but with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
insert into BranchA (a,b) values (1,'log1');
insert into BranchB (a,b) values (1,'log2');

To get combined results

select * from BranchSUMMARY;
Hope this helps.

Regards,

Mike Chirico
Jul 20 '05 #5

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA, but with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
insert into BranchA (a,b) values (1,'log1');
insert into BranchB (a,b) values (1,'log2');

To get combined results

select * from BranchSUMMARY;
Hope this helps.

Regards,

Mike Chirico
Jul 20 '05 #6

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA, but with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
insert into BranchA (a,b) values (1,'log1');
insert into BranchB (a,b) values (1,'log2');

To get combined results

select * from BranchSUMMARY;
Hope this helps.

Regards,

Mike Chirico
Jul 20 '05 #7
> CREATE TABLE BranchSUMMARY_summary (

That's BranchSUMMARY without "_summary" which was a typo on my part.

CREATE TABLE BranchSUMMARY (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;

Regards,

Mike Chirico
Jul 20 '05 #8
> CREATE TABLE BranchSUMMARY_summary (

That's BranchSUMMARY without "_summary" which was a typo on my part.

CREATE TABLE BranchSUMMARY (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;

Regards,

Mike Chirico
Jul 20 '05 #9
> CREATE TABLE BranchSUMMARY_summary (

That's BranchSUMMARY without "_summary" which was a typo on my part.

CREATE TABLE BranchSUMMARY (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;

Regards,

Mike Chirico
Jul 20 '05 #10
Ike
Ok, but, what if certain fields are links to other tables, which you are
merging as well? How do you know what to add to the fields (assuming these
keys are integers) -Ike

"Mike Chirico" <mc******@comcast.net> wrote in message
news:Qq********************@comcast.com...

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,

but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
insert into BranchA (a,b) values (1,'log1');
insert into BranchB (a,b) values (1,'log2');

To get combined results

select * from BranchSUMMARY;
Hope this helps.

Regards,

Mike Chirico

Jul 20 '05 #11
Ike
Ok, but, what if certain fields are links to other tables, which you are
merging as well? How do you know what to add to the fields (assuming these
keys are integers) -Ike

"Mike Chirico" <mc******@comcast.net> wrote in message
news:Qq********************@comcast.com...

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,

but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
insert into BranchA (a,b) values (1,'log1');
insert into BranchB (a,b) values (1,'log2');

To get combined results

select * from BranchSUMMARY;
Hope this helps.

Regards,

Mike Chirico

Jul 20 '05 #12
Ike
Ok, but, what if certain fields are links to other tables, which you are
merging as well? How do you know what to add to the fields (assuming these
keys are integers) -Ike

"Mike Chirico" <mc******@comcast.net> wrote in message
news:Qq********************@comcast.com...

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,

but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;
insert into BranchA (a,b) values (1,'log1');
insert into BranchB (a,b) values (1,'log2');

To get combined results

select * from BranchSUMMARY;
Hope this helps.

Regards,

Mike Chirico

Jul 20 '05 #13
"Mike Chirico" <mc******@comcast.net> wrote in message
news:Qq********************@comcast.com...

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,

but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;


I found this to be quite odd until I looked at your web reference to confirm
my suspicions:

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(BranchA,BranchB) INSERT_METHOD=LAST;

The original was will referring to log_x files from your examples at your
web site.

- Virgil
Jul 20 '05 #14
"Mike Chirico" <mc******@comcast.net> wrote in message
news:Qq********************@comcast.com...

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,

but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;


I found this to be quite odd until I looked at your web reference to confirm
my suspicions:

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(BranchA,BranchB) INSERT_METHOD=LAST;

The original was will referring to log_x files from your examples at your
web site.

- Virgil
Jul 20 '05 #15
"Mike Chirico" <mc******@comcast.net> wrote in message
news:Qq********************@comcast.com...

"Ike" <rx*@hotmail.com> wrote in message
news:LZ****************@newsread2.news.pas.earthli nk.net...
Suppose I have a relational database, called "BranchA."

I have a second relational database, of identical structure to BranchA,

but
with different data, and this one is called BranchB.

Can I merge the two, into, say, a BranchC somehow on occassion?


Yes, several tables can be merged into a summary table, and you can still
keep
BranchA, and BranchB...

See below or reference (TIP 8) at the following url:
http://osdn.dl.sourceforge.net/sourc...ADME_mysql.txt

CREATE TABLE BranchA (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchB (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MyISAM;

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(log_01,log_02) INSERT_METHOD=LAST;


I found this to be quite odd until I looked at your web reference to confirm
my suspicions:

CREATE TABLE BranchSUMMARY_summary (
pkey int(11) NOT NULL auto_increment,
a int,
b varchar(12),
timeEnter timestamp(14),
PRIMARY KEY (pkey)
) type=MERGE UNION(BranchA,BranchB) INSERT_METHOD=LAST;

The original was will referring to log_x files from your examples at your
web site.

- Virgil
Jul 20 '05 #16

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Patrick | last post: by
3 posts views Thread by abprules | last post: by
3 posts views Thread by Ralph Smith | last post: by
9 posts views Thread by karenjfrancis | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.