[dba-SQLServer] Automating the creation of views

Francisco / Betsy,

Thank you both for your replies.  Francisco, I read the article you 
provided, but I'm still stuck.  I'm able to successfully transport the 
results of the View from the source database to a table in the target 
database using the SSIS wizard. 
However, what I want to do is take a view from the source database, such 
as:  "SELECT * FROM dbo.MySourceTable WHERE MyField = 'xxx'"

and create a new view in the target database like this:   "SELECT * FROM 
SourceDatabase.dbo.MySourceTable WHERE MyField = 'xxx'"

... or alternatively, I suppose it could be:  "SELECT * FROM 
SourceDatabase.dbo.MyView"

Does anyone know of a way to have the view copied into the target database 
as a view?

Thanks,
Stu




=====================================================

dba-sqlserver-request at databaseadvisors.com 
Sent by: dba-sqlserver-bounces at databaseadvisors.com
04/23/2009 12:00 PM
Please respond to
dba-sqlserver at databaseadvisors.com


To
dba-sqlserver at databaseadvisors.com
cc

Subject
dba-SQLServer Digest, Vol 74, Issue 5





Today's Topics:

   1.  Come visit (Arthur Fuller)
   2.  Automating the creation of views (sdunlap at brownshoe.com)
   3. Re:  Automating the creation of views (Powlen, Betsy)
   4. Re:  Automating the creation of views (Francisco Tapia)
   5. Re:  Automating the creation of views (Francisco Tapia)


------------------------------

Message: 2
Date: Thu, 23 Apr 2009 08:20:31 -0500
From: sdunlap at brownshoe.com
Subject: [dba-SQLServer] Automating the creation of views
To: dba-sqlserver at databaseadvisors.com
Message-ID:
 <OF0ED1E0DE.B929888E-ON862575A1.0042A759-862575A1.00494B9D at brownshoe.com>
 
Content-Type: text/plain; charset="US-ASCII"

Hi,

I'm new to SQL Server and I've been tasked with creating a lot of views 
(over 100).  I'm using SQL Server 2005.

The process of creating these views will be repetitive, and I'm wondering 
if there is some way to automate the creation of the views.

More details on the task at hand...I have a whole bunch of views in a 
database, View1, View2, View3 ....... ViewN.  For the purposes of this 
example, this database would be called 'OriginalDatabase'.

In another database, the views I need to create will be:    SELECT  * FROM 

 OriginalDatabase.dbo.ViewX

In the SQL above, ViewX represents the view 1, 2, 3 or N.  I would want to 

be able to generate the list of Views (1, 2, 3 ... N) by programmatically 
reading the available views in the OriginalDatabase views folder and 
selecting only the views I need based on a naming convention - 
specifically, the first three characters of the view name = 'nnn'.

I have a book on SQL Server 2005, but I haven't been successful using the 
index to find help on this, mainly because I'm not sure what topic this 
would fall under. 

Does anyone know if it is possible to automate the creation of the views?

Any insight is appreciated.

Thanks,
Stu

------------------------------

Message: 3
Date: Thu, 23 Apr 2009 09:33:03 -0400
From: "Powlen, Betsy" <Betsy.Powlen at stanleyassociates.com>
Subject: Re: [dba-SQLServer] Automating the creation of views
To: "Discussion concerning MS SQL Server"
                 <dba-sqlserver at databaseadvisors.com>
Message-ID:
 <2A419A21A2A23243A9E7851EEDBDC122B0896D at OP-S-EX-2.stanleyassociates.com>
 
Content-Type: text/plain;                charset="us-ascii"

Hi Stu,
It sounds to me like you want to copy certain views to your new
database. Is this correct? If so, you can use Data Transformation
Service (DTS) from Enterprise Manager. With this, you can export
whatever views you need and the dependant objects all in one action.
Betsy
 
------------------------------

Message: 4
Date: Thu, 23 Apr 2009 07:11:11 -0700
From: Francisco Tapia <fhtapia at gmail.com>
Subject: Re: [dba-SQLServer] Automating the creation of views
To: Discussion concerning MS SQL Server
                 <dba-sqlserver at databaseadvisors.com>
Message-ID:
 <b874372a0904230711t5d09b95du1e257b19dfd11dda at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

he is in sql server 2005 from what I read, thus DTS won't really help
connecting, plus technically you can't create any new dts jobs on sql 
server
2005 (yes you can always duplicate an existing dts package to create a new
one, but I digress).  In this instance there is a ssis wizard that would
help you copy the views you want to your new database.

-Francisco
http://sqlthis.blogspot.com | Tsql and More...

------------------------------

Message: 5
Date: Thu, 23 Apr 2009 07:12:34 -0700
From: Francisco Tapia <fhtapia at gmail.com>
Subject: Re: [dba-SQLServer] Automating the creation of views
To: Discussion concerning MS SQL Server
                 <dba-sqlserver at databaseadvisors.com>
Message-ID:
 <b874372a0904230712x74e92f4p89e88602b973fadd at mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1

For info on the wizard just follow this link...

http://sqlthis.blogspot.com/2009/03/ssis-sos.html
-Francisco
http://sqlthis.blogspot.com | Tsql and More...