CODEWITHSUNDEEP
sqlsql-server
wafaa
wafaa

Reputation: 1

Why do we need to create views when we can simply use SELECT INTO?

Why do we need to create views while we can simply use select into?

For example:

select table1.columnX,...,table2.columnY 
into newtable 
from table1,table2

Upvotes: 0

Views: 89

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

SELECT INTO creates a copy of the query results in a new table. This becomes, essentially, a fixed snapshot of the query results at that time, unless you build mechanisms to keep the results up to date (such as a DML trigger on the base table(s)).

A view, on the other hand, doesn't store data at all.* It's just a query, not used for performance reasons usually, but rather to simplify access to underlying data by creating a reusable module, perhaps also to implement security in some way (e.g. deny SELECT on underlying tables and only expose certain columns and/or rows in the view).

*-With the exception of indexed views, which materialize the results.

Also, please don't use bad, bad, bad inner join syntax that doesn't specify INNER JOIN.

Upvotes: 3

Related Questions