SQL Server 2008 R2 storing queries as views; Copy/Paste objects

Question

1. In SQL Server 2008 R2, if you click the New Query button, you get an empty Editor. Here you can code SQL and store it in an external text file with .sql extension. Is there a way that I can also save it as a view inside the database?
2. Also, when editing a View, I only get the Save button/Menu option, there is no Save As, so, if I want to create a copy of a View I cannot do it. There should be a Copy/Paste option for creating copies of tables/views (like in Access where you can do this and you are prompted to paste table with structure only, or with structure and data).
3. Can I import an external .sql file, so it is then stored as a view within the database?

Seems like Access has some advanced features that are very helpful and that SQL Server is missing. Odd, as SQL Server is supposed to be a more advanced system.

---

Adrian Hernandez

Answer 1

A view is nothing more than a SELECT query that is stored in a database.  You do not "save" the view, but rather write your query and it with CREATE VIEW <<VIEW_NAME>> AS.  once this statement is execute the view will be saved in the databse that it is created in:

USE AdventureWorks2012;
GO
CREATE VIEW names
AS
SELECT FirstName,
       MiddleName,
       LastName
FROM Person.Person;
GO

This view can then be selected from using

SELECT *
FROM names;
GO

Hope this helps

---

David Dye My Blog

Answer 2

on tables,views right click, then go to script it as ... cretae alter what ever..

copy paste what ever , do control S and save as .sql ...  or other 2 questions, David Dyle answered, on how to create a view...

---

Please mark as helpful and propose as answer if you find this as correct!!! Thanks,Dia Agha .

Answer 3

Still, when I follow the steps described above, the only option to save it is as an external .sql file. There should be an option to store it as a view, otherwise I need to constantly open external files. To me this is a blatant ommission by Microsoft. Storing queries as external files can be dangerous as they can be deleted. Storing them inside the database is more convinient. I guess you should have both alternatives.

PS : While coding in the New Query Window, SQL marks all table and query names as incorrect (wavy red line underneath the name), yet, the code runs perfectly well. Seems like Management Studio is disconnected from the Database so it doesn't recognize the DB ojects. When you write your code in the View Window, it's different, Management studio has intellisense and can fill in table, views, and other object names for you. To me, this is another in a long list of things left out by Microsoft, or bugs.

---

Adrian Hernandez

Answer 4

What I meant by saving the view is that it's stored as an object, w/o the need to use Create View... After all, I already coded the SQL statements in the New Query Window.

I know there are other ways of accomplishing the same tasks, I guess my concern is that Microsoft made some very important and obvious ommisions when developing the SQL Server Management Studio.

Are there any add-ons that I can download/buy, that would add this functionality to SQL Server Managemente Studio?

---

Adrian Hernandez

Answer 5

Right click and choose the Script as CREATE TO.  The will create the DDL statement to recreate the view which can then be execute in the desired server/database.

---

David Dye My Blog