Search code examples
sqlsql-serverselectcreate-table

Why I can't create a new table using simply query CREATE TABLE?

MS SQL SERVER
When I do query

SELECT *
FROM dbo.transactions

it list all rows and colums perfectly.

Problem is when I'm trying to do a new table with CREATE TABLE

CREATE TABLE dbo.all_transactions 
SELECT *
FROM dbo.transactions

or

CREATE TABLE dbo.all_transactions AS
SELECT *
FROM dbo.transactions

Always I get strange error

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SELECT'

There is only * next to SELECT. How it could be an "Incorrect syntax"?

I tried changing the table name or using another table, but it doesn't work. I checked the names of the columns are unique. They are unique.

Solution

  • Suppose you want to create table all_transactions copying the data from transactions Try out this:

    SELECT * INTO dbo.all_transactions
FROM dbo.transactions;