CODEWITHSUNDEEP
sql-serversql-server-2008create-table
Syed Siraj Uddin
Syed Siraj Uddin

Reputation: 593

Try to create a table from Select - SqL Server 2008 throws error

Hi I am trying to create a table using inner select statement...

for example: 

CREATE TABLE JmxMonSer AS (SELECT * FROM services WHERE monitoring_enabled = 1);

But keep getting error:

Incorrect Syntax near keyword 'AS', Severity 15

please advice

Upvotes: 2

Views: 39191

Answers (4)

MLow
MLow

Reputation: 739

Try using SELECT INTO:

SELECT *
INTO newtable [IN externaldb]
FROM table1;

src: http://www.w3schools.com/sql/sql_select_into.asp

Upvotes: 1

Code
Code

Reputation: 61

The below syntax is for using sub-query instead of using static table name... because sometime the required result set is coming from different queries..

SELECT * 
INTO JmxMonSer
From (SELECT * FROM services WHERE monitoring_enabled = 1) as X

Upvotes: 2

LittleBobbyTables - Au Revoir
LittleBobbyTables - Au Revoir

Reputation: 32690

I'm almost positive that SQL Server doesn't have a CREATE TABLE AS (SELECT... syntax, but you can use SELECT INTO:

SELECT * 
INTO JmxMonSer
FROM services 
WHERE monitoring_enabled=1

Check the MSDN documentation for proper uses of the CREATE TABLE statement.

Upvotes: 13

Adam Batkin
Adam Batkin

Reputation: 52994

How about:

SELECT * into JmxMonSer FROM services WHERE monitoring_enabled=1

If the table already exists (and the columns types and ordering line up), you can use:

INSERT INTO JmxMonSer SELECT * FROM services WHERE monitoring_enabled=1

Upvotes: 10

Related Questions