Create Table from View

Create Table from View

SQL Server does not support CREATE TABLE AS SELECT.

Use this:

SELECT  *
INTO    A
FROM    myview

or

SELECT  TOP 10
        *
INTO    A
FROM    myview
ORDER BY
        id

Easy 'create table from view' syntax in mysql?

You can do CREATE TABLE SELECT from the view to build it. That should duplicate the view's structure as a new table containing all the view's rows. Here's the MySQL syntax reference for this statement.

CREATE TABLE tbl_from_view AS    
  SELECT
    col1,
    col2,
    col3,
    col4,
    col5
  FROM your_view;

Note that you will want to be very explicit in your column selections. It isn't advisable to do a SELECT * from the source view. Make sure as well that you have aliases for any calculated or aggregate columns like COUNT(*), MAX(*), (col1 + col2), etc.

Is it possible to script a view as a table in SQL Server?

Not out of the box. You can do SELECT INTO from the View to create a new empty table then script that as a CREATE TABLE using the usual SSMS methods.

SELECT TOP 0 * INTO NewTable FROM YourView

This might save a bit of typing if that's the motivation for the question.

How to create table/view with data from another SQL server?

You could create the linked server, if the server of third party vendor is SQL Server, follow the following steps. If the server of third party vendor is not SQL Server, you need to choose other data source, and sometimes need to install the OLE DB driver.

First, enter the connection path of the third party vendor.

Second, enter the account and password.

Third, create the view.

The query format will be

SELECT Column1, Column2 ...
FROM [server path].[db name].[dbo].[table name]

Reference: How to create and configure a linked server in SQL Server Management Studio

How do I store my 'view' as a permanent table in mySQL?

You could do 3 things:
1> create a view (youi'll refer to the view as if it was a table)

create view my_new_view as
select `Date`, `Close Price`,
round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) 
as '20 Day MA' 
from table1; 

then read data with select * from myview

If you need a 'snapshot' of the table you can 'materialize' with select into

CREATE TABLE my_new_table -- mysql syntax
select `Date`, `Close Price`,
round(avg(`Close Price`) over (order by `date` asc rows 19 preceding), 2) 
as '20 Day MA' 
-- into my_new_table --this forces the creation of new table -- sorry: SQL SERVER syntax
from table1

A third option is to add the new column than call an 'update' to populate it

---

Related Topics