Copy rows from multiple tables into one table (in another database)

My boss has been using linked tables (read only) from Access to copy data
from our accounting system to his pc. Instead of using Access I would like
to copy rows from different tables into a new table in a different database.
I want to use a different database for security reasons because the new
database will be accessed by the web server.
What is the best way to simulate Access' linked tables and can the
resolution be realtime or would I need to run a query once a day to have the
most current info in my new table?You can either use DTS or Replication depends on how your boss want to see
the data as exactly current or could be sometime delay.
Perayu
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||It looks like I should use SQL triggers to update information in new table
when changes have been made in the accounting database. What is the best
way to export data rows from different tables to one new table and does
anyone have an example of how to create a trigger that watches many tables,
but only inserts into one table?
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||Sometime delayed is fine with us. I'll research those two topics, but which
is less complicated seeing as I'm not a SQL guru.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:O%23lvOifvFHA.3100@.TK2MSFTNGP12.phx.gbl...
> It looks like I should use SQL triggers to update information in new table
> when changes have been made in the accounting database. What is the best
> way to export data rows from different tables to one new table and does
> anyone have an example of how to create a trigger that watches many
> tables, but only inserts into one table?
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||In my opinion, DTS is a easier one. Sometime, Replication is hard to
maintain whenever you try to change the related data structure.
Perayu
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:Oi4bWnfvFHA.2132@.TK2MSFTNGP15.phx.gbl...
> Sometime delayed is fine with us. I'll research those two topics, but
> which is less complicated seeing as I'm not a SQL guru.
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:O%23lvOifvFHA.3100@.TK2MSFTNGP12.phx.gbl...
>|||The functional equivalent to Access's linked tables is to set up a Linked
Server in SQL Server. A linked server can be any OLEDB data source. Any
tables in the remote system would be available through this technique.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
> My boss has been using linked tables (read only) from Access to copy data
> from our accounting system to his pc. Instead of using Access I would
> like to copy rows from different tables into a new table in a different
> database. I want to use a different database for security reasons because
> the new database will be accessed by the web server.
> What is the best way to simulate Access' linked tables and can the
> resolution be realtime or would I need to run a query once a day to have
> the most current info in my new table?
>
>|||I tried DTS but didn't find anyway to choose which rows in a table I want.
It seemed to be the whole table or nothing at all. The other problem was
data from the accounting system would wipe out the target destination data
and I want them to merge.
Can I choose certain rows from different tables and merge them into one
table with Linked Server?
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:uFybfFhvFHA.2504@.TK2MSFTNGP15.phx.gbl...
> The functional equivalent to Access's linked tables is to set up a Linked
> Server in SQL Server. A linked server can be any OLEDB data source. Any
> tables in the remote system would be available through this technique.
> "JohnS" <JohnSPAM@.hotmail.com> wrote in message
> news:e4d43AfvFHA.3256@.TK2MSFTNGP09.phx.gbl...
>|||Well, DTS and linked servers are kind of "apples and oranges" topics.
In DTS, you can use regular SQL statements to define which columns and rows
you want to retrieve from your source connection, and you can define where
that data gets placed in your destination connection. DTS is a fairly large
topic with many sub-topics. I would encourage you to thouroughly review the
topic in SQL Server Books Online (BOL).
As for linked servers, all I was pointing out was that you can set up a
linked server in SQL Server in a somewhat similar fashion to the way you set
up linked tables in MS Access. Again, this is a moderately involved topic.
For more information, see the topic "Accessing External Data" in BOL.
"JohnS" <JohnSPAM@.hotmail.com> wrote in message
news:eXBFQPivFHA.904@.tk2msftngp13.phx.gbl...
>I tried DTS but didn't find anyway to choose which rows in a table I want.
>It seemed to be the whole table or nothing at all. The other problem was
>data from the accounting system would wipe out the target destination data
>and I want them to merge.
> Can I choose certain rows from different tables and merge them into one
> table with Linked Server?
>
> "Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
> news:uFybfFhvFHA.2504@.TK2MSFTNGP15.phx.gbl...
>