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

Question

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.

Create table requires the column information syntax ref: https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16 for your reading pleasure. — Mark Schultheiss, Mar 31 '23 at 19:33
You have to follow the correct syntax. You can't just write whatever, and expect SQL Server to somehow understand what you want. — Eric, Mar 31 '23 at 22:55

score 2 · Accepted Answer · edited Mar 31 '23 at 18:09

2

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

SELECT * INTO dbo.all_transactions
FROM dbo.transactions;

edited Mar 31 '23 at 18:09

Gary_W

answered Mar 31 '23 at 15:35

1 Answers1