I'm investigating replication or DTS as a possible solution to my
problem. I'm looking to replicate data from one server to another,
I want all of the columns of one table PLUS another column from
another table to transfer to the other server.
For example on Server A, I have a table foo. Table Foo has column1
and column2. Also on Server A I have a table bar. Table bar has a
column1 and a column3. Server B has a table called test which has
column1, column2 and column3.
I want to replicate to the test table on Server B,
foo.column1, foo.column2, bar.column3
where foo.column1 = bar.column1
Is this possible?
I looked into replicating a view, but this requires the underlying
tables to reside at the subscriber which I do not want.
Can you replicate the output of a stored procedure to another
server? I see references to replicating stored procedures, but can't
figure out how to implement this. The only options I have for
articles are Tables and Views, why don't I see any stored procedures
as articles to publish?
Maybe it makes more sense just to use DTS?
You don't talk about whether this is a one shot deal or if you want to run this multiple times or how large the table is.
If its a one shot deal you should use DTS. Its faster than replication.
If your table is small and changes infrequently you could do this with snapshot or transactional replication.
If your table is large and you want some level of real time synchronization you should use transactional replication.
You can extend transactional replication to do lookups and replicate additional columns by creating a custom sync object. To do this create your own creation script using sp_addarticle.
Here is an example of how to do this.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment