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?

select * into A from dbo.myView where 1 = 2 will give you an empty table — t-clausen.dk, Jul 14 '11 at 14:18
Your question answered on this [Question](https://stackoverflow.com/questions/21547/in-sql-server-how-do-i-generate-a-create-table-statement-for-a-given-table). [See this answer will help you](https://stackoverflow.com/a/317864/4104167). script on this answer get you scripts for generate all tables in database. If you modify last where condition, you can get scripts for create table from views. [The next answer](https://stackoverflow.com/a/21551/4104167) makes it easier for you. You can set table or view name and run script then result return create table script for you. — amin, Jul 16 '17 at 10:31

Quassnoi · Accepted Answer · 2011-07-14T14:17:40.550

116

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

edited Jul 14 '11 at 14:17

answered Jul 14 '11 at 14:08

Quassnoi

413,100
91
616
614

VIews to big to select *. How can I say top 10? – tdjfdjdj Jul 14 '11 at 14:15
Is there any way to prevent a connection timeout without limiting results with this method? – vizyourdata May 29 '18 at 18:52
2

@vizyourdata: run it as an SQL Server agent job – Quassnoi May 29 '18 at 23:06
2

good one but the one below is better if you dont need the data, just the schema – kuklei Dec 17 '19 at 13:26

score 61 · Answer 2 · answered Jul 14 '11 at 14:21

61

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

answered Jul 14 '11 at 14:21

Pittsburgh DBA

6,672
2
39
68

1

... WHERE false; work fine. – Barrrettt May 05 '22 at 14:39

score 14 · Answer 3 · answered Jul 14 '11 at 14:08

14

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.

answered Jul 14 '11 at 14:08

Daniel Hilgarth

171,043
40
335
443

score 7 · Answer 4 · answered Jul 14 '11 at 14:11

7

To create a table on the fly us this syntax:

SELECT *
INTO A
FROM dbo.myView

answered Jul 14 '11 at 14:11

Keith

2,618
4
29
44

score 3 · Answer 5 · answered Jul 14 '11 at 14:07

3

If you want to create a new A you can use INTO;

select * into A from dbo.myView

answered Jul 14 '11 at 14:07

Alex K.

171,639
30
264
288

score 3 · Answer 6 · answered Jul 14 '11 at 14:08

3

SELECT * INTO [table_a] FROM dbo.myView

answered Jul 14 '11 at 14:08

mikey

5,090
3
24
27

score 3 · Answer 7 · edited Jan 02 '15 at 19:31

3

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)

edited Jan 02 '15 at 19:31

Brian

6,910
8
44
82

answered Jul 14 '11 at 14:10

MatBailie

83,401
18
103
137

score 1 · Answer 8 · edited Feb 04 '15 at 15:13

1

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

edited Feb 04 '15 at 15:13

andrewsi

10,807
132
35
51

answered Feb 04 '15 at 14:52

javal

11
1

score -1 · Answer 9 · edited Apr 02 '20 at 20:22

-1

INSERT INTO table 2
SELECT * FROM table1/view1

edited Apr 02 '20 at 20:22

Zoe

27,060
21
118
148

answered Oct 29 '15 at 21:38

amstegraf

597
1
5
11

1

a verbal explanation is often helpful – con Apr 03 '20 at 17:10
This will insert rows in to an existing table, not create a new table. – MatBailie Dec 06 '22 at 10:56

Create Table from View

9 Answers9

Linked

Related