Create Table from View

Question

I have a view that I want to create a table from in SQL Enterprise Manager, but I always get an error when I run this query:

CREATE TABLE A 
AS
(SELECT top 10 FROM dbo.myView)

So far the error is: "syntax error at 'as'"

View is too large. Is it possible to use a top 10?

Answer 1

Looks a lot like Oracle, but that doesn't work on SQL Server.

You can, instead, adopt the following syntax...

SELECT
  *
INTO
  new_table
FROM
  old_source(s)

Answer 2

If you just want to snag the schema and make an empty table out of it, use a false predicate, like so:

SELECT * INTO myNewTable FROM myView WHERE 1=2

Answer 3

Select 
    MonthEndDate MED,  
    SUM(GrossBalance/1000000) GrossBalance,
    PortfolioRename PR 
into 
    testDynamic 
from 
    Risk_PortfolioOverview  
    Group By MonthEndDate, PortfolioRename

Answer 4

In SQL SERVER you do it like this:

SELECT *
INTO A
FROM dbo.myView

This will create a new table A with the contents of your view.
See here for more info.

Answer 5

INSERT INTO table 2 SELECT * FROM table1/view1

Answer 6

To create a table on the fly us this syntax:

SELECT *
INTO A
FROM dbo.myView