CODEWITHSUNDEEP
sql-serverssis
StevieB
StevieB

Reputation: 6541

SSIS Moving Content From Views from one database to Another

One question though lets say publisher database had 100 tables and I use Transactional Replication to move the data from those 100 tables to Subscriber Database that would be fine.

But lets say I don't want the 100 tables but i want to create 3-4 Views which contain the key information I want from those 100 tables. How Would I achieve this.

1) Firstly I guess the views need to be created on the publisher database 2) Secondly Do i need to create then 3/4 Tables in the Subscriber database which have the same columns as the view from publisher database. 3) What sort of replication or maybe even SSIS or something to move the data from the publisher view to subscriber database

Upvotes: 0

Views: 1150

Answers (2)

bobs
bobs

Reputation: 22214

SSIS will be the better solution. You would create the tables on your target database. Then, you can create the SSIS pacakge(s) to populate the target tables.

SSIS can use queries on tables or views. And, it can also execute a stored procedure to retrieve the data.

Upvotes: 0

James Alexander
James Alexander

Reputation: 6312

Replication probably wouldn't be viable or as performant an option as creating a SSIS package for transferring data from those views and into the small set of tables in the remote database. SSIS's strongest feature is it's ability to transfer large volumes of data quickly from a source and into a destination. With a little upkeep, you could potentially just transfer the differences between the two databases at some scheduled interval and have a fairly flexible solution.

Upvotes: 1

Related Questions