Copying data from on database to another

Hi,
I currently have 3 databases, master_database, database_one and
database_two. Both database_one and database_two contain a number of
different tables recording different information, some of these tables
need to be backed up to the master_database. Within the master_database
is a table that contains a list of tables that need to be updated.
Basically the master_database contains all the information where
database_one and database_two periodically delete the oldest rows so
that the table in the two database are not too large. So far I've
created a store proceedure in the master_database that goes through the
list of tables and if the tables is not present in the master_database
then it is created and populated with the data in the original table
(be it in either database_one or database_two). However I'm having
difficulty with if the table is present in the master_database on how
to update it, only the rows that are not present in the master_database
need to be copied. I currently have:
insert into table1 select * from database_one.dbo.table1
where database_one.dbo.table1.timestamp >(select max(timestamp) from
table1)
however the problem with this is that it doesnt update all the rows and
it looks at timestamp and depending on the table then there are
differet primary keys.
Any advise on how to get arround this would be much appreciated.
Thanks
Simon
Read up on replication in Books online. That can do everything you have
described.
Jacco Schalkwijk
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128431426.829767.109970@.o13g2000cwo.googlegr oups.com...
> Hi,
> I currently have 3 databases, master_database, database_one and
> database_two. Both database_one and database_two contain a number of
> different tables recording different information, some of these tables
> need to be backed up to the master_database. Within the master_database
> is a table that contains a list of tables that need to be updated.
> Basically the master_database contains all the information where
> database_one and database_two periodically delete the oldest rows so
> that the table in the two database are not too large. So far I've
> created a store proceedure in the master_database that goes through the
> list of tables and if the tables is not present in the master_database
> then it is created and populated with the data in the original table
> (be it in either database_one or database_two). However I'm having
> difficulty with if the table is present in the master_database on how
> to update it, only the rows that are not present in the master_database
> need to be copied. I currently have:
> insert into table1 select * from database_one.dbo.table1
> where database_one.dbo.table1.timestamp >(select max(timestamp) from
> table1)
> however the problem with this is that it doesnt update all the rows and
> it looks at timestamp and depending on the table then there are
> differet primary keys.
> Any advise on how to get arround this would be much appreciated.
> Thanks
> Simon
>
|||Hi,
I know that its possible to do what I'm asking through replication but
I want to use a store procedure as its going to run every hour to
update the master_database. Any ideas on how it can be done in store
procedures?
Thanks
Simon
|||What are the specific reasons to use a stored procedure? You can schedule
replication.
Jacco Schalkwijk
SQL Server MVP
"accyboy1981" <accyboy1981@.gmail.com> wrote in message
news:1128432063.229252.248410@.z14g2000cwz.googlegr oups.com...
> Hi,
> I know that its possible to do what I'm asking through replication but
> I want to use a store procedure as its going to run every hour to
> update the master_database. Any ideas on how it can be done in store
> procedures?
> Thanks
> Simon
>
|||Hi,
The reasoning is that my boss wants it in a store procedure and not as
replication. From what I've read I would choose replication but in the
current situation I am unable to do so.
Simon