• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

copy views to another database in Micorsoft SQL

When I tried to use DTS to copy views, instead of copying views, it created new tables based on the views and did not copy views.

There must be a code out there that can do the work.

Thansk.
0
oldbridge
Asked:
oldbridge
  • 3
  • 2
  • 2
  • +1
3 Solutions
 
Reza RadCommented:
in sql server management studio
just right click on your first database and go to tasks and then generate sql scripts
create scripts for views
and then run this script on your destination database


0
 
jogosCommented:
Copying a view is only copying the definition of a view.

Exec sp_helptext 'myviewname'

will give you the view-definition.  The result you get you can execute on the other database.
0
 
oldbridgeAuthor Commented:
That will require to copy one view at a time. There is no mechanisam to copy all views? There must be some way. I am surprise that Microsoft did not think of offering simple copy tool.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
oldbridgeAuthor Commented:
I need to keep the same view names in the new database. We use excel to create reports that are linked to views. Each year database is indepedent. All we do is change year in excel to refer to new database. That is why the best option is to be able to copy all views to new database with same name.
0
 
Reza RadCommented:
the way that I suggest to you will do this for all views! just do it

but there is another way too:
create an SSIS package ( in business intelligence development studio - BIDS create a SSIS package) this is new version of old DTS pacakge .
in this package add a Transfer SQL Server Objects Task. in this task you can set source server, destination server and all objects you want to transfer, you can select all views for example
0
 
jogosCommented:
The sp_helptext solution is perfectly suitable for all views, thats why is suggested it. Just put it in a curosr-loop (http://msdn.microsoft.com/en-us/library/ms180169.aspx) on all the views you want (http://msdn.microsoft.com/en-us/library/ms181381.aspx) .
Output to a file and you have your 'initial view creation script' that you can review and store (source controle?) before executing.
A bit more complex is getting it executed immediate, but I strongly advise not to do this. How do you ever know what you have done?
(and yes I try to let you gain knowledge, not to be cheap labor. So I won't try to type a possible sollution I don't have in a copy-paste format at the moment)


0
 
Anthony PerkinsCommented:
>>That will require to copy one view at a time. <<
Why?

>>There is no mechanisam to copy all views?<<
If you are talking about doing it with SQL Server Management system then all you have to do is script them all out at one time.  You can do this in under one minute.

>>I am surprise that Microsoft did not think of offering simple copy tool.<<
They did.  See above.
0
 
Anthony PerkinsCommented:
P.S.  DTS is overkill for such a simple task and besides the tool is deprecated in SQL Server 2005 in favor of SSIS.
0
 
jogosCommented:
3 suitable sollutions posted (2 by reza_rad, one by me but reza_rad was first).
And 'acperkins' asked why on a first 'not suitable' comment, but author didn't respond.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now