Hi,

I want to transfer some views from one server to another.
But I want the views to be materialized into tables on the
destination server. The views are different with different
structures (different number of columns, name...etc). I
have to materialize them as tables in order to improve
reporting performance.

I used the DTS Import/Export wizard by selecting the "Copy
table(s) and view(s) from the source database" option and
selecting the views to copy over. It works perfect and the
views are created at the destination as tables (instead of
views). This is just what I want. DTS brilliantly
generates the required DDL and creates the table and then
populates it.

Now, I want to create a package that would automatically
(on a scheduled basis) take a new or updated view from the
source and create it as a table in the destination.

I am able to use SQL in an 'Execute SQL' task to get the
views that I require and use a recordset object to store
the view names. I loop through this recordset and
dynamically move them over to the destination using the
Transfer Object Task. But the problem is that they are now
moved over and created at the destination as views instead
of being materialized as tables at the destination.

How do I mimic what the DTS Import/Export wizard does by
using the DTS designer? I can't use the 'Transform Data
Task' (DataPump) because it looks like it can't
dynamically generate the DDL to create the view as a table
in the destination.

The 'Transfer Object Task' can drop and create
tables/views at the destination but it looks like it can't
transfer the views as tables. It transfers views as views.

Even if I can figure a way to automatically generate DDL
(Create Table) statements for a given view, I think I
could make it work.

BTW, for security purposes, my company does not allow
linked servers between the source and destination server.
So, I can't use a pure SQL solution with four part naming
to solve this.

Any help is appreciated! Thanks!

In message <254101c50ec2$afb55cb0$a501280a (AT) phx (DOT) gbl>, Vinod
<anonymous (AT) discussions (DOT) microsoft.com> writes
The wizard cannot be automated, and there is no task that creates tables
from source objects for your in this way. Also there is no normal task
that automatically generates a DataPump dynamically as the wizard does.

You could do this by writing your own code to enumerate views, get the
structure, generate the create table, then generate the package to move
the data. A fair bit of work, but all possible. Sorry, I can't think of
a better way.




--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org