Create Table from View
SqlTsqlViewSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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
Solution 3 - Sql
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.
Solution 4 - Sql
To create a table on the fly us this syntax:
SELECT *
INTO A
FROM dbo.myView
Solution 5 - Sql
If you want to create a new A you can use INTO;
select * into A from dbo.myView
Solution 6 - Sql
SELECT * INTO [table_a] FROM dbo.myView
Solution 7 - Sql
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)
Solution 8 - Sql
Select
MonthEndDate MED,
SUM(GrossBalance/1000000) GrossBalance,
PortfolioRename PR
into
testDynamic
from
Risk_PortfolioOverview
Group By MonthEndDate, PortfolioRename
Solution 9 - Sql
INSERT INTO table 2
SELECT * FROM table1/view1